How to create a trace (Transact-SQL)

To create a trace

1. Execute xp_trace_addnewqueue with the required parameters to create a trace queue and determine the columns to record.

   ---

   Note SQL Server Profiler automatically and invisibly creates a trace queue.

   ---

2. Execute xp_trace_seteventclassrequired with the required parameters to select the events to trace.
3. Optionally, execute the applicable xp_trace_set* extended stored procedures to set any, none, or a combination of filters.

   ---

   Important There are multiple procedures beginning with the string xp_trace_set. When you see xp_trace_set*, this refers to all trace procedures beginning with this string.

   ---

4. Execute xp_trace_setqueuedestination with the required parameters to select a consumer for the trace data.
5. Optionally, execute xp_trace_savequeuedefinition to save the trace queue definition.
6. Execute xp_trace_startconsumer with the required parameters to start the consumer, which sends the trace queue information to the chosen destination.

Example

This example creates a trace called All Data for Query Analyzer that traces the specified events issued from SQL Server Query Analyzer. The example saves the queue definition in the registry as a shared queue definition.

USE master

DECLARE @queue_handle int

-- A value of 0 for the 5th parameter of xp_trace_addnewqueue provides

-- all columns in the output.

EXEC xp_trace_addnewqueue 1000, 5, 95, 90, 0, @queue_handle OUTPUT

EXEC xp_trace_seteventclassrequired @queue_handle, 29, 1

EXEC xp_trace_setappfilter @queue_handle, 'SQL Server Query Analyzer',
    NULL

-- Insert any applicable xp_trace_set* procedures

-- Set the queue destination to a file using Compact ANSI

EXEC xp_trace_setqueuedestination @queue_handle, 2, 1, NULL,

    'c:\mssql7\data\trace.txt'

-- Saving the definition is optional and can be done later, or in

-- repeated places, and is shared.

EXEC xp_trace_savequeuedefinition @queue_handle,

    'All Data for Query Analyzer', 1

-- Start the trace queue consumer

EXEC xp_trace_startconsumer @queue_handle

See Also