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 Lockeadlock 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