Create a Trace (Transact-SQL)

Article
02/28/2023

Applies to: SQL Server

This topic describes how to use stored procedures to create a trace.

To create a trace

Execute sp_trace_create with the required parameters to create a new trace. The new trace will be in a stopped state (status is 0).
Execute sp_trace_setevent with the required parameters to select the events and columns to trace.
Optionally, execute sp_trace_setfilter to set any or a combination of filters.

sp_trace_setevent and sp_trace_setfilter can be executed only on existing traces that are stopped.

Important

Unlike regular stored procedures, parameters of all SQL Server Profiler stored procedures (sp_trace_xx) are strictly typed and do not support automatic data type conversion. If these parameters are not called with the correct input parameter data types, as specified in the argument description, the stored procedure returns an error.

Examples

The following code demonstrates creating a trace using Transact-SQL. It is in three sections: creating the trace, populating the trace file, and stopping the trace. Customize the trace by adding the events that you want to trace. For the list of events and columns, see sp_trace_setevent (Transact-SQL).

A. Create a trace

The following code creates a trace, adds events to the trace, and then starts the trace:

DECLARE @RC int, @TraceID int, @on BIT  
EXEC @rc = sp_trace_create @TraceID output, 0, N'C:\SampleTrace'  
  
-- Select the return code to see if the trace creation was successful.  
SELECT RC = @RC, TraceID = @TraceID  
  
-- Set the events and data columns you need to capture.  
SELECT @on = 1  
  
-- 10 is RPC:Completed event. 1 is TextData column.   
EXEC sp_trace_setevent @TraceID, 10, 1, @on   
-- 13 is SQL:BatchStarting, 11 is LoginName  
EXEC sp_trace_setevent @TraceID, 13, 11, @on   
-- 13 is SQL:BatchStarting, 14 is StartTime  
EXEC sp_trace_setevent @TraceID, 13, 14, @on   
-- 12 is SQL:BatchCompleted, 15 is EndTime  
EXEC sp_trace_setevent @TraceID, 12, 15, @on   
-- 13 is SQL:BatchStarting, 1 is TextData  
EXEC sp_trace_setevent @TraceID, 13, 1, @on   
  
-- Set any filter. Not provided in this example  
--EXEC sp_trace_setfilter 1, 10, 0, 6, N'%Profiler%'  
  
-- Start Trace (status 1 = start)  
EXEC @RC = sp_trace_setstatus @TraceID, 1  
GO

B. Populate the trace file

Now that the trace has been created and started, execute the following code to populate the trace with activity.

SELECT * FROM master.sys.databases  
GO  
SELECT * FROM ::fn_trace_getinfo(default)  
GO

C. Stop the trace

The trace can be stopped and restarted at any time. In this example, execute the following code to stop the trace, close the trace, and delete the trace definition.

DECLARE @TraceID int  
-- Populate a variable with the trace_id of the current trace  
SELECT  @TraceID = TraceID FROM ::fn_trace_getinfo(default) WHERE VALUE = N'C:\SampleTrace.trc'  
  
-- First stop the trace.   
EXEC sp_trace_setstatus @TraceID, 0  
  
-- Close and then delete its definition from SQL Server.   
EXEC sp_trace_setstatus @TraceID, 2

D. Examine the trace file

To examine the trace file, open the SampleTrace.trc file using SQL Server Profiler.