This is the default text

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?

 

 

, , , ,