Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Recording Traces Without Profiler

You can create traces without Profiler’s user interface by executing system stored procedures. First, you must create a trace using sp_trace_create, then add events using sp_trace_setevent, and set filters using sp_trace_setfilter. Finally, use the sp_trace_setstatus procedure to start, stop, close, and delete the trace. All of these procedures are well-documented in Books Online.

Here is an example of creating the trace, recording a few events, and starting and stopping the trace:

/* variable to hold a trace ID */
DECLARE @trace_id INT

EXEC sp_trace_create 
 @traceid = @trace_id OUTPUT, 
 @options = 2,
 @tracefile = N’c:\trace_example.trc’ 

SELECT @trace_id AS trace_id

/* define some events for the trace we just created */

DECLARE @on BIT, @tid INT
SELECT @on=1, @tid = 1

/* add text data column for SQL: BatchCompleted event */
EXEC sp_trace_setevent @traceid = @tid, 
@eventid = 12, @columnid = 1, @on = @on

GO

DECLARE @on BIT, @tid INT
SELECT @on=1, @tid = 1

/* add text data column for RPC: completed event */
EXEC sp_trace_setevent @traceid = @tid, 
@eventid = 10, @columnid = 1, @on = @on

GO

DECLARE @on BIT, @tid INT
SELECT @on=1, @tid = 1

/* add application name column for SQL: Batch Completed event */
EXEC sp_trace_setevent @traceid = @tid, 
@eventid = 12, @columnid = 10, @on = @on

/* create a filter for the existing trace */


DECLARE @tid INT
SELECT @tid = 1

EXEC sp_trace_setfilter @traceid = @tid, 
@columnid = 10, @logical_operator = 1, 
@comparison_operator = 7, @value = N’SQL Profiler’

/* turn on the trace */
sp_trace_setstatus 1, 1

/* stop the trace */

sp_trace_setstatus 1, 0
GO

/* delete trace definition */
Sp_trace_setstatus 1, 2

Figure 5 shows the trace that was saved to my C drive.

Figure 5

Figure 5 A trace that was generated without using the Profiler user interface.

  • + Share This
  • 🔖 Save To Your Account