Tag Archives | SQL Server 2008

Project Lucy investigates your SQL Performance for free

I would think that most of the people in the SQL Server community that are active in Blogging or Tweeting have heard about “Project Lucy” by now. I will start by giving you what they define the project as:

“Project Lucy is part infrastructure analytics, part social media experiment, and part market data warehouse. With the performance data you provide us and we evaluate, we’ll be able to provide you with unparalleled knowledge about the performance of your applications, as well as compare it to that of similar businesses.”

The program is still in its infancy, but the coolness factor is definitely there. Basically, you go and grab the Profiler Trace template for the version of SQL Server that you want to trace, read a little bit about how things work and you are good to begin.  One thing to note is that the trace templates are for use from within SQL Profiler 2008.

If you are like me, you have quite a few SQL Server 2000 instances that are still in play. And if you’ve tried tuning SQL 2000 at all then you know that you don’t have all the tools that were born out of SQL 2005 and later products.  With great anticipation, I downloaded the SQL Server 2000 Trace Template and opened up SQL Profiler 2008. Well, evidently it wasn’t going to be as easy as I thought. I got the following love note:

projectLucySQL2000Error

That red X can never be good.

I got some time to play around with it so I looked at the process used for automating the trace. I decided to open up the trace that I downloaded for SQL 2008 and see what the export options looked like.

projectLucySQL2008ExportOptions

Learned a new trick with this one.

Well, I guess I was onto something because after looking at .sql file as well as the page on automating I was able to piece something together that didn’t throw an error. By running a server side trace, I was able to get it to run without giving an error. I haven’t dug into what all it changed, but I do know that by doing it this way, you lose the Auto Grow/Auto Shrink stats that can be analyzed on Project Lucy.

It may be cool to see a comparison chart for sp_trace_setevent for SQL 2000 and later, and I guess I can add that to the list of things I want to blog about but don’t because I don’t make enough time for it. Guess I’ll have to start the draft for that one as soon as I get this one out to push myself. Ok, back to getting a good .trc or .zip file to submit to Project Lucy for SQL 2000.

add squirrel

Yes, I'm so ADD.

After cleaning up the code, and before knowing that the Auto Grow/Auto Shrink weren’t included (based on the Analysis saying it wasn’t) I was left with the code below. I’ve had a little trouble getting Syntax Highlighter working, but I believe it’s working now.

-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
DECLARE @expiryTime DATETIME
DECLARE @minutesToTrace INT

/*
Please replace the text InsertFileNameHere, with an appropriate
filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
will be appended to the filename automatically. If you are writing from
remote server to local drive, please use UNC path and make sure server has
write access to your network share
*/

SET @minutesToTrace = 1
--set @maxfilesize = 250
SET @expiryTime = DATEADD(mi, @minutesToTrace, GETDATE())
SET @maxfilesize = 20

-- Reminder to put a \\UNC\Share here.
EXEC @rc = sp_trace_create
@TraceID OUTPUT ,
0 ,
N'InsertFileNameHere' ,
@maxfilesize ,
@expiryTime
IF ( @rc != 0 )
GOTO error
-- Client side File and Table cannot be scripted

-- Set the events
-- SQL Server Yukon specific events will not be scripted
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 67, 7, @on
exec sp_trace_setevent @TraceID, 67, 31, @on
exec sp_trace_setevent @TraceID, 67, 8, @on
exec sp_trace_setevent @TraceID, 67, 1, @on
exec sp_trace_setevent @TraceID, 67, 9, @on
exec sp_trace_setevent @TraceID, 67, 21, @on
exec sp_trace_setevent @TraceID, 67, 41, @on
exec sp_trace_setevent @TraceID, 67, 6, @on
exec sp_trace_setevent @TraceID, 67, 10, @on
exec sp_trace_setevent @TraceID, 67, 14, @on
exec sp_trace_setevent @TraceID, 67, 26, @on
exec sp_trace_setevent @TraceID, 67, 3, @on
exec sp_trace_setevent @TraceID, 67, 11, @on
exec sp_trace_setevent @TraceID, 67, 35, @on
exec sp_trace_setevent @TraceID, 67, 4, @on
exec sp_trace_setevent @TraceID, 67, 12, @on
exec sp_trace_setevent @TraceID, 55, 7, @on
exec sp_trace_setevent @TraceID, 55, 8, @on
exec sp_trace_setevent @TraceID, 55, 9, @on
exec sp_trace_setevent @TraceID, 55, 21, @on
exec sp_trace_setevent @TraceID, 55, 25, @on
exec sp_trace_setevent @TraceID, 55, 41, @on
exec sp_trace_setevent @TraceID, 55, 6, @on
exec sp_trace_setevent @TraceID, 55, 10, @on
exec sp_trace_setevent @TraceID, 55, 14, @on
exec sp_trace_setevent @TraceID, 55, 22, @on
exec sp_trace_setevent @TraceID, 55, 26, @on
exec sp_trace_setevent @TraceID, 55, 3, @on
exec sp_trace_setevent @TraceID, 55, 11, @on
exec sp_trace_setevent @TraceID, 55, 35, @on
exec sp_trace_setevent @TraceID, 55, 4, @on
exec sp_trace_setevent @TraceID, 55, 12, @on
exec sp_trace_setevent @TraceID, 79, 7, @on
exec sp_trace_setevent @TraceID, 79, 8, @on
exec sp_trace_setevent @TraceID, 79, 1, @on
exec sp_trace_setevent @TraceID, 79, 9, @on
exec sp_trace_setevent @TraceID, 79, 41, @on
exec sp_trace_setevent @TraceID, 79, 6, @on
exec sp_trace_setevent @TraceID, 79, 10, @on
exec sp_trace_setevent @TraceID, 79, 14, @on
exec sp_trace_setevent @TraceID, 79, 26, @on
exec sp_trace_setevent @TraceID, 79, 3, @on
exec sp_trace_setevent @TraceID, 79, 11, @on
exec sp_trace_setevent @TraceID, 79, 35, @on
exec sp_trace_setevent @TraceID, 79, 4, @on
exec sp_trace_setevent @TraceID, 79, 12, @on
exec sp_trace_setevent @TraceID, 80, 7, @on
exec sp_trace_setevent @TraceID, 80, 4, @on
exec sp_trace_setevent @TraceID, 80, 8, @on
exec sp_trace_setevent @TraceID, 80, 12, @on
exec sp_trace_setevent @TraceID, 80, 9, @on
exec sp_trace_setevent @TraceID, 80, 41, @on
exec sp_trace_setevent @TraceID, 80, 6, @on
exec sp_trace_setevent @TraceID, 80, 10, @on
exec sp_trace_setevent @TraceID, 80, 14, @on
exec sp_trace_setevent @TraceID, 80, 26, @on
exec sp_trace_setevent @TraceID, 80, 3, @on
exec sp_trace_setevent @TraceID, 80, 11, @on
exec sp_trace_setevent @TraceID, 80, 35, @on
exec sp_trace_setevent @TraceID, 69, 7, @on
exec sp_trace_setevent @TraceID, 69, 8, @on
exec sp_trace_setevent @TraceID, 69, 9, @on
exec sp_trace_setevent @TraceID, 69, 21, @on
exec sp_trace_setevent @TraceID, 69, 41, @on
exec sp_trace_setevent @TraceID, 69, 6, @on
exec sp_trace_setevent @TraceID, 69, 10, @on
exec sp_trace_setevent @TraceID, 69, 14, @on
exec sp_trace_setevent @TraceID, 69, 26, @on
exec sp_trace_setevent @TraceID, 69, 3, @on
exec sp_trace_setevent @TraceID, 69, 11, @on
exec sp_trace_setevent @TraceID, 69, 35, @on
exec sp_trace_setevent @TraceID, 69, 4, @on
exec sp_trace_setevent @TraceID, 69, 12, @on
exec sp_trace_setevent @TraceID, 51, 7, @on
exec sp_trace_setevent @TraceID, 51, 8, @on
exec sp_trace_setevent @TraceID, 51, 24, @on
exec sp_trace_setevent @TraceID, 51, 9, @on
exec sp_trace_setevent @TraceID, 51, 41, @on
exec sp_trace_setevent @TraceID, 51, 6, @on
exec sp_trace_setevent @TraceID, 51, 10, @on
exec sp_trace_setevent @TraceID, 51, 14, @on
exec sp_trace_setevent @TraceID, 51, 22, @on
exec sp_trace_setevent @TraceID, 51, 26, @on
exec sp_trace_setevent @TraceID, 51, 3, @on
exec sp_trace_setevent @TraceID, 51, 11, @on
exec sp_trace_setevent @TraceID, 51, 35, @on
exec sp_trace_setevent @TraceID, 51, 4, @on
exec sp_trace_setevent @TraceID, 51, 12, @on
exec sp_trace_setevent @TraceID, 52, 7, @on
exec sp_trace_setevent @TraceID, 52, 8, @on
exec sp_trace_setevent @TraceID, 52, 16, @on
exec sp_trace_setevent @TraceID, 52, 24, @on
exec sp_trace_setevent @TraceID, 52, 9, @on
exec sp_trace_setevent @TraceID, 52, 41, @on
exec sp_trace_setevent @TraceID, 52, 6, @on
exec sp_trace_setevent @TraceID, 52, 10, @on
exec sp_trace_setevent @TraceID, 52, 14, @on
exec sp_trace_setevent @TraceID, 52, 22, @on
exec sp_trace_setevent @TraceID, 52, 26, @on
exec sp_trace_setevent @TraceID, 52, 3, @on
exec sp_trace_setevent @TraceID, 52, 11, @on
exec sp_trace_setevent @TraceID, 52, 35, @on
exec sp_trace_setevent @TraceID, 52, 4, @on
exec sp_trace_setevent @TraceID, 52, 12, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 41, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 4, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 11, 7, @on
exec sp_trace_setevent @TraceID, 11, 8, @on
exec sp_trace_setevent @TraceID, 11, 1, @on
exec sp_trace_setevent @TraceID, 11, 9, @on
exec sp_trace_setevent @TraceID, 11, 41, @on
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 6, @on
exec sp_trace_setevent @TraceID, 11, 10, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 26, @on
exec sp_trace_setevent @TraceID, 11, 34, @on
exec sp_trace_setevent @TraceID, 11, 3, @on
exec sp_trace_setevent @TraceID, 11, 11, @on
exec sp_trace_setevent @TraceID, 11, 35, @on
exec sp_trace_setevent @TraceID, 11, 4, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 38, 7, @on
exec sp_trace_setevent @TraceID, 38, 8, @on
exec sp_trace_setevent @TraceID, 38, 1, @on
exec sp_trace_setevent @TraceID, 38, 9, @on
exec sp_trace_setevent @TraceID, 38, 41, @on
exec sp_trace_setevent @TraceID, 38, 6, @on
exec sp_trace_setevent @TraceID, 38, 10, @on
exec sp_trace_setevent @TraceID, 38, 14, @on
exec sp_trace_setevent @TraceID, 38, 22, @on
exec sp_trace_setevent @TraceID, 38, 26, @on
exec sp_trace_setevent @TraceID, 38, 34, @on
exec sp_trace_setevent @TraceID, 38, 3, @on
exec sp_trace_setevent @TraceID, 38, 11, @on
exec sp_trace_setevent @TraceID, 38, 35, @on
exec sp_trace_setevent @TraceID, 38, 4, @on
exec sp_trace_setevent @TraceID, 38, 12, @on
exec sp_trace_setevent @TraceID, 38, 28, @on
exec sp_trace_setevent @TraceID, 35, 7, @on
exec sp_trace_setevent @TraceID, 35, 8, @on
exec sp_trace_setevent @TraceID, 35, 1, @on
exec sp_trace_setevent @TraceID, 35, 9, @on
exec sp_trace_setevent @TraceID, 35, 41, @on
exec sp_trace_setevent @TraceID, 35, 6, @on
exec sp_trace_setevent @TraceID, 35, 10, @on
exec sp_trace_setevent @TraceID, 35, 14, @on
exec sp_trace_setevent @TraceID, 35, 22, @on
exec sp_trace_setevent @TraceID, 35, 26, @on
exec sp_trace_setevent @TraceID, 35, 3, @on
exec sp_trace_setevent @TraceID, 35, 11, @on
exec sp_trace_setevent @TraceID, 35, 35, @on
exec sp_trace_setevent @TraceID, 35, 4, @on
exec sp_trace_setevent @TraceID, 35, 12, @on
exec sp_trace_setevent @TraceID, 35, 28, @on
exec sp_trace_setevent @TraceID, 34, 7, @on
exec sp_trace_setevent @TraceID, 34, 8, @on
exec sp_trace_setevent @TraceID, 34, 1, @on
exec sp_trace_setevent @TraceID, 34, 9, @on
exec sp_trace_setevent @TraceID, 34, 41, @on
exec sp_trace_setevent @TraceID, 34, 6, @on
exec sp_trace_setevent @TraceID, 34, 10, @on
exec sp_trace_setevent @TraceID, 34, 14, @on
exec sp_trace_setevent @TraceID, 34, 22, @on
exec sp_trace_setevent @TraceID, 34, 26, @on
exec sp_trace_setevent @TraceID, 34, 34, @on
exec sp_trace_setevent @TraceID, 34, 3, @on
exec sp_trace_setevent @TraceID, 34, 11, @on
exec sp_trace_setevent @TraceID, 34, 4, @on
exec sp_trace_setevent @TraceID, 34, 12, @on
exec sp_trace_setevent @TraceID, 34, 28, @on
exec sp_trace_setevent @TraceID, 43, 7, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 41, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 26, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 4, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 28, @on
exec sp_trace_setevent @TraceID, 43, 5, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 29, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 37, 7, @on
exec sp_trace_setevent @TraceID, 37, 8, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 41, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 26, @on
exec sp_trace_setevent @TraceID, 37, 34, @on
exec sp_trace_setevent @TraceID, 37, 3, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 35, @on
exec sp_trace_setevent @TraceID, 37, 4, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 5, @on
exec sp_trace_setevent @TraceID, 37, 21, @on
exec sp_trace_setevent @TraceID, 37, 29, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 22, @on
exec sp_trace_setevent @TraceID, 45, 7, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 25, @on
exec sp_trace_setevent @TraceID, 45, 41, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 26, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 4, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 5, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 29, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 44, 7, @on
exec sp_trace_setevent @TraceID, 44, 8, @on
exec sp_trace_setevent @TraceID, 44, 1, @on
exec sp_trace_setevent @TraceID, 44, 9, @on
exec sp_trace_setevent @TraceID, 44, 41, @on
exec sp_trace_setevent @TraceID, 44, 10, @on
exec sp_trace_setevent @TraceID, 44, 26, @on
exec sp_trace_setevent @TraceID, 44, 34, @on
exec sp_trace_setevent @TraceID, 44, 3, @on
exec sp_trace_setevent @TraceID, 44, 11, @on
exec sp_trace_setevent @TraceID, 44, 35, @on
exec sp_trace_setevent @TraceID, 44, 4, @on
exec sp_trace_setevent @TraceID, 44, 12, @on
exec sp_trace_setevent @TraceID, 44, 28, @on
exec sp_trace_setevent @TraceID, 44, 5, @on
exec sp_trace_setevent @TraceID, 44, 29, @on
exec sp_trace_setevent @TraceID, 44, 6, @on
exec sp_trace_setevent @TraceID, 44, 14, @on
exec sp_trace_setevent @TraceID, 44, 22, @on
exec sp_trace_setevent @TraceID, 44, 30, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 41, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 4, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 41, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 4, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 41, 7, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 25, @on
exec sp_trace_setevent @TraceID, 41, 41, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 26, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 4, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 5, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 29, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 40, 7, @on
exec sp_trace_setevent @TraceID, 40, 8, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 9, @on
exec sp_trace_setevent @TraceID, 40, 41, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 26, @on
exec sp_trace_setevent @TraceID, 40, 30, @on
exec sp_trace_setevent @TraceID, 40, 3, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 35, @on
exec sp_trace_setevent @TraceID, 40, 4, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 5, @on
exec sp_trace_setevent @TraceID, 40, 29, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - b67e1234-32cd-4c79-af08-0ce2556fa6d3'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

By using the code above you can at least generate a one minute trace or a 20MB trace, whichever comes first. You can play around with the values to get more info, and possibly even add the Auto Shrink/Auto Grow info in there. If I get the time I will come back here and change it with that update.

Example Analysis

I’d like to hear from some of you. Is there a better way to do this? Anything I’m missing?

 

 

Comments { 2 } Posted on June 9, 2011 in SQL General, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication

Weekly Reading Roundup 01.18.11

I want to begin a new process of sharing items I am reading or find that might be useful to others. I’m trying to break it down into 3 levels based on how important it can be. Try not to get hung up on which level I put it into, since I’m just trying to give more weight to certain links from my opinion.

Gold Level
iPhone Photo Management 101
This one has truly revolutionized the way I handle my pictures on the iPhone. I take tons of pictures and it can be a bit of trouble to scroll through all of them and find something I want to show someone. You could always just keep them on your computer, but if you have kids you’ll probably agree that your phone can become your digital wallet.

Pining a .BAT/.CMD File to Taskbar
If you are doing any type of Systems Administration then you will obviously find a need for this.

Wait statistics, or please tell me where it hurts
Another good resource for anyone dealing with SQL performance issues related to waits and queues.

Silver Level
The difference between the Microsoft’s Hyper-V and the VMware’s ESX hypervisor

7 ways to organize your email
I need to do a whole series on this one. If you can get an email triage going combined with GTD integration you’ll be going a long way to keeping things running smoothly.

5 Tips for Getting the Most out of Google Reader
I’ve been cleaning out my Google Reader lately and this can be a pretty helpful article.

Bronze Level
Motivational Posters: Founding Fathers Edition

17 Free Inspirational iPhone Backgrounds

The P&G 1-page memo

Comments { 1 } Posted on January 18, 2011 in Education, General, Links for the Week, SQL Server 2005, SQL Server 2008

SQL Server 2008, MAPS and VMWare

I came accross this post from Buck Woody recently, and I thought it would be an excellent opportunity to try out SQL Server 2008 on a VM and serve multiple purposes at the same time. Well, even though my VM has 4GB of RAM allocated, the MAPS install fails with insufficient Physical RAM (95 MB). I’m not really sure how to get around this but if anyone has any ideas I’d love to hear them.

Comments { 0 } Posted on May 18, 2009 in SQL Server 2008

Which SQL Server version?

From my understanding, it is only necessary for me to discuss SQL Server versions later than SQL Server 2000. I include SQL Server 2000 because the truth is that many people are still using this product. I know that the shop I work in has mostly SQL Server 2000 instances and a couple of SQL Server 2005 so that is where I’m coming from. Some of the consideration may be out of your control because your shop may not have a SQL Server 2000 server. I hope to present the pros and cons that go with the order in which you choose to begin your journey. I also hope that others will add their opinion to back me up or correct me.

SQL Server 2008
If you aren’t working with a certain SQL Server version at your workplace then I would think that going with SQL Server 2008 would be your best option. It’s the latest and greatest and you will give yourself the most shelf-life for your effort. SQL Server 2008 was released in August of 2008 and you can see a build list here. One negative I noticed is that some of the books (MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 Implementation and Maintenance (PRO-Certification) are still being finalized for this version at the time of this writing. At the bare minimum, I would recommend becoming familiar with this version and go grab the Express Edition.

SQL Server 2005
You still may want to look at this version first if you are breaking into the field. There are tons of resources for SQL Server 2005 and you can focus on laying the ground work for a successful base to your knowledge. SQL Server 2008 does have new features, but I am not sure they are make or break for the beginner DBA. Personally, I have only gone as far as touching SQL Server 2005 and 2000. I am in the process of beginning to test SQL Server 2008, but since it’s not deployed in my work environment I feel that I gain more by focusing on maintaining and upgrading from 2000 to 2005. The amount of resources available to someone starting out with 2005 will be far greater than 2008 at the time of this writing, but there are many variables to add up when making your decision and I can’t account for all of them. One negative aspect that I saw last night would be finding a development version of SQL Server 2005 to start your test lab with. Amazon did not have any copies in stock so you would have to find a reseller if you wanted to use more advanced features that weren’t available in the Express Edition. SQL Server 2005 is up to SP3 so you are past the initial growing pains that come with a product without a service pack. You can grab the Express Edition here.

SQL Server 2000
Support for this product is only available through extended support. If you are currently in a shop that primarily uses SQL Server 2000 then you first step should be learning how to help maintain what is already there. If you are going to be working with a product then it does not make sense to me to only prepare for the future. This is the situation that I inherited so I can speak more clearly on it than the others. When this package fell into my lap we were only using SQL Server 2000. Even though the product life cycle is basically expired, the product still has to be managed by someone. Given the current economy, there’s a pretty good chance that budgets are frozen and upgrades to SQL Server 2005 may not be possible. This does not mean you should not prepare for the future, but it does mean you better become an expert on what your shop is using right now if you want to be the “go-to-guy” where you are at. There are tons of resources out there for SQL Server 2000, and that has helped me a great deal when it came to becomming the person who wants to answer the questions. If your shop is currently using SQL Server 2000 then you should have access to the necessary materials to build a good test lab to begin the learning process. I made the choice to go ahead and go with the 2005 certification track since that is the direction we are going, but I still needed to know how to do the same things in 2000. Having a certification of MCITP Database Administrator in SQL Server 2005 helps give those around me the confidence that I am able to facilitate an upgrade from SQL Server 2000 to 2005.

Summary
The SQL Server version you choose to go with definitely depends on variables that only you know. Whether or not you actually get to touch the SQL Servers at your workplace also play a huge role in what kind of decision you make. I would still download an Express Edition copy of 2005 and 2008 in order to get your feet wet, but you have to focus on what you feel will give you the edge where you work or where you would like to work. It all goes back to establishing what your goal is and then taking action to make it happen. If you want to become a SQL DBA where you work then you have to know if there’s even a possibility of that happening before you expend too much energy going down that path. Pick a target and set some goals to try and hit it. If you never set them then you’ll miss it every time. And by all means, if you want different results then you have to change what you are doing.

Comments { 0 } Posted on February 17, 2009 in SQL Server 2000, SQL Server 2005, SQL Server 2008