Trace Not Collecting Data
-
I'm learning how to create and execute Profiler traces using the system stored procedures. Here is the code for my first trace:
<code>
DECLARE @rc int, @TraceID int, @On BIT
EXEC @rc = sp_trace_create @TraceID output, 2,
N'L:\MSSQL$SQL2000\Logs\FarmerJack_Trace'
-- Store @TraceID to be able to stop trace later
SELECT TraceID = @TraceID
-- Variable used to store the value of the @on parameter of sp_trace_setevent
SELECT @On = 1
-- Turn on monitoring of the RPC:Completed event (eventid 10)
-- Syntax: EXEC sp_trace_setevent @TraceID, @eventid, @columnid, @on
EXEC sp_trace_setevent @TraceID, 10, 1, @On -- Text Data
EXEC sp_trace_setevent @TraceID, 10, 2, @On -- Binary Data
EXEC sp_trace_setevent @TraceID, 10, 3, @On -- DatabaseID
EXEC sp_trace_setevent @TraceID, 10, 4, @On -- TransactionID
EXEC sp_trace_setevent @TraceID, 10, 6, @On -- NTUserName
EXEC sp_trace_setevent @TraceID, 10, 7, @On -- NTDomainName
EXEC sp_trace_setevent @TraceID, 10, 8, @On -- ClientHostName
EXEC sp_trace_setevent @TraceID, 10, 9, @On -- ClientProcessID
EXEC sp_trace_setevent @TraceID, 10, 10, @On -- ApplicationName
EXEC sp_trace_setevent @TraceID, 10, 12, @On -- SPID
EXEC sp_trace_setevent @TraceID, 10, 13, @On -- Duration
EXEC sp_trace_setevent @TraceID, 10, 15, @On -- EndTime
-- Turn on monitoring of the SQL:BatchCompleted event (eventid 12)
-- Syntax: EXEC sp_trace_setevent @TraceID, @eventid, @columnid, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @On -- Text Data
EXEC sp_trace_setevent @TraceID, 12, 2, @On -- Binary Data
EXEC sp_trace_setevent @TraceID, 12, 3, @On -- DatabaseID
EXEC sp_trace_setevent @TraceID, 12, 4, @On -- TransactionID
EXEC sp_trace_setevent @TraceID, 12, 6, @On -- NTUserName
EXEC sp_trace_setevent @TraceID, 12, 7, @On -- NTDomainName
EXEC sp_trace_setevent @TraceID, 12, 8, @On -- ClientHostName
EXEC sp_trace_setevent @TraceID, 12, 9, @On -- ClientProcessID
EXEC sp_trace_setevent @TraceID, 12, 10, @On -- ApplicationName
EXEC sp_trace_setevent @TraceID, 12, 12, @On -- SPID
EXEC sp_trace_setevent @TraceID, 12, 13, @On -- Duration
EXEC sp_trace_setevent @TraceID, 12, 15, @On -- EndTime
-- Turn on monitoring of the Lock
eadlock event (eventid 25)-- Syntax: EXEC sp_trace_setevent @TraceID, @eventid, @columnid, @on
EXEC sp_trace_setevent @TraceID, 25, 1, @On -- Text Data
EXEC sp_trace_setevent @TraceID, 25, 2, @On -- Binary Data
EXEC sp_trace_setevent @TraceID, 25, 3, @On -- DatabaseID
EXEC sp_trace_setevent @TraceID, 25, 4, @On -- TransactionID
EXEC sp_trace_setevent @TraceID, 25, 6, @On -- NTUserName
EXEC sp_trace_setevent @TraceID, 25, 7, @On -- NTDomainName
EXEC sp_trace_setevent @TraceID, 25, 8, @On -- ClientHostName
EXEC sp_trace_setevent @TraceID, 25, 9, @On -- ClientProcessID
EXEC sp_trace_setevent @TraceID, 25, 10, @On -- ApplicationName
EXEC sp_trace_setevent @TraceID, 25, 12, @On -- SPID
EXEC sp_trace_setevent @TraceID, 25, 13, @On -- Duration
EXEC sp_trace_setevent @TraceID, 25, 15, @On -- EndTime
-- Turn on monitoring of the SQL:StmtCompleted event (eventid 41)
-- Syntax: EXEC sp_trace_setevent @TraceID, @eventid, @columnid, @on
EXEC sp_trace_setevent @TraceID, 41, 1, @On -- Text Data
EXEC sp_trace_setevent @TraceID, 41, 2, @On -- Binary Data
EXEC sp_trace_setevent @TraceID, 41, 3, @On -- DatabaseID
EXEC sp_trace_setevent @TraceID, 41, 4, @On -- TransactionID
EXEC sp_trace_setevent @TraceID, 41, 6, @On -- NTUserName
EXEC sp_trace_setevent @TraceID, 41, 7, @On -- NTDomainName
EXEC sp_trace_setevent @TraceID, 41, 8, @On -- ClientHostName
EXEC sp_trace_setevent @TraceID, 41, 9, @On -- ClientProcessID
EXEC sp_trace_setevent @TraceID, 41, 10, @On -- ApplicationName
EXEC sp_trace_setevent @TraceID, 41, 12, @On -- SPID
EXEC sp_trace_setevent @TraceID, 41, 13, @On -- Duration
EXEC sp_trace_setevent @TraceID, 41, 15, @On -- EndTime
-- Turn on monitoring of the Object:Created event (eventid 46)
-- Syntax: EXEC sp_trace_setevent @TraceID, @eventid, @columnid, @on
EXEC sp_trace_setevent @TraceID, 46, 1, @On -- Text Data
EXEC sp_trace_setevent @TraceID, 46, 2, @On -- Binary Data
EXEC sp_trace_setevent @TraceID, 46, 3, @On -- DatabaseID
EXEC sp_trace_setevent @TraceID, 46, 4, @On -- TransactionID
EXEC sp_trace_setevent @TraceID, 46, 6, @On -- NTUserName
EXEC sp_trace_setevent @TraceID, 46, 7, @On -- NTDomainName
EXEC sp_trace_setevent @TraceID, 46, 8, @On -- ClientHostName
EXEC sp_trace_setevent @TraceID, 46, 9, @On -- ClientProcessID
EXEC sp_trace_setevent @TraceID, 46, 10, @On -- ApplicationName
EXEC sp_trace_setevent @TraceID, 46, 12, @On -- SPID
EXEC sp_trace_setevent @TraceID, 46, 13, @On -- Duration
EXEC sp_trace_setevent @TraceID, 46, 15, @On -- EndTime
-- Filter out events for applications beginning with MS%
EXEC sp_trace_setfilter @traceid=1, @columnid=10, @logical_operator=0, @comparison_operator=6, @value=N'MS%'
-- Filter out events for the trace itself
EXEC sp_trace_setfilter @traceid=1, @columnid=10, @logical_operator=0, @comparison_operator=6, @value=N'SQLT%'
-- Start the trace (status 1 = start)
EXEC sp_trace_setstatus @traceid, 1
-- View information on all running traces
SELECT * from ::fn_trace_getinfo(default)
</code>
When I run SELECT * from ::fn_trace_getinfo(default), I see that the trace is running. Here is the output
traceid property value
1 1 2
1 2 L:\MSSQL$SQL2000\Logs\FarmerJack_Trace
1 3 5
1 4 NULL
1 5 1
(5 row(s) affected)
However, when I run some test SQL statements, no data passes through to the trace. What am I doing wrong?
“If you're not outraged at the media, you haven't been paying attention.”
-
Moderator:
Please move this post to the SQL Server General forum. I clicked the wrong link when I created this post.
“If you're not outraged at the media, you haven't been paying attention.”
-
You need to shut down the trace and close it before viewing.
exec sp_trace_setstatus 2,0
exec sp_trace_setstatus 2,2
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply