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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
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">quote:<hr height="1" noshade id="quote">Originally posted by Haywood Urgh, I am such an idiot… I somehow cutoff the "exec sp_trace_setstatus @TraceID, 1"…. <hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important Bertrand Russell All postings are provided â€œAS ISâ€ with no warranties for accuracy.

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."

]]>