Trace help… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Trace help…

I’m trying to automate an index usage trace. I setup the trace in profiler, validated that it’s what I want and then scripted the trace out. However, the procedure isn’t capturing any data and I cannot for the life of me figure out why. I do this type of thing all the time, this is the first time it’s never worked… Here’s the trace procedure, any ideas folks? CREATE PROCEDURE usp_IndexUsage_Audit @FileName nvarchar(255), @Trace_ID int AS — Create a Queue
DECLARE @rc int
DECLARE @TraceID int
DECLARE @MaxFileSize bigint
DECLARE @StopTime datetime
DECLARE @iError INT SET @MaxFileSize = 1024
SELECT @StopTime = (SELECT CONVERT(CHAR(11),getdate()) + ‘ 23:59:59.997’) EXEC @rc = sp_trace_create @TraceID output, 2 — FileRollover
, @FileName, @MaxFileSize, @StopTime IF (@rc != 0) BEGIN RAISERROR (‘Error with the sp_trace_create’, 16, 1) END ELSE BEGIN declare @on bit
set @on = 1 — Event 23: Lock Release
exec sp_trace_setevent @TraceID, 23, 3, @on — DB_ID()
exec sp_trace_setevent @TraceID, 23, 12, @on — SPID
exec sp_trace_setevent @TraceID, 23, 22, @on — ObjectID
exec sp_trace_setevent @TraceID, 23, 24, @on — IndexID — Event 52: Scan Stop
exec sp_trace_setevent @TraceID, 52, 3, @on
exec sp_trace_setevent @TraceID, 52, 12, @on
exec sp_trace_setevent @TraceID, 52, 22, @on
exec sp_trace_setevent @TraceID, 52, 24, @on
— Set the Filters
declare @intfilter int
declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Profiler’ set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter END
GO — execution:
EXEC Admin..usp_IndexUsage_Audit ‘\YourUNCPath’, NULL
Urgh, I am such an idiot… I somehow cutoff the "exec sp_trace_setstatus @TraceID, 1"….
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Haywood</i><br /><br />Urgh, I am such an idiot… I somehow cutoff the "exec sp_trace_setstatus @TraceID, 1"….<br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />Luis Martin<br />Moderator<br /><br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
I hate doing something that stupid and not being able to see it… I’d been staring at it too long I guess. "Can’t see the forest because the trees are getting in the way."