␡
- Profiler Trace Contents
- Profiler Templates
- Saving and Examining Trace Output
- Replaying Previously Recorded Traces
- Recording Traces Without Profiler
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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 A trace that was generated without using the Profiler user interface.