Create Manual Traces using Stored Procedures

Applies to: SQL Server

Microsoft SQL Server provides Transact-SQL system stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within your own applications to create traces manually, instead of using SQL Server Profiler. This allows you to write custom applications specific to the needs of your enterprise.

In This Section

The following table lists the system stored procedures for tracing an instance of the SQL Server Database Engine.

Stored procedure Task performed
sys.fn_trace_geteventinfo (Transact-SQL) Returns information about events included in a trace.
sys.fn_trace_getinfo (Transact-SQL) Returns information about a specified trace or all existing traces.
sp_trace_create (Transact-SQL) Creates a trace definition. The new trace will be in a stopped state.
sp_trace_generateevent (Transact-SQL) Creates a user-defined event.
sp_trace_setevent (Transact-SQL) Adds an event class or data column to a trace, or removes one from it.
sp_trace_setstatus (Transact-SQL) Starts, stops, or closes a trace.
sys.fn_trace_getfilterinfo (Transact-SQL) Returns information about filters applied to a trace.
sp_trace_setfilter (Transact-SQL) Applies a new or modified filter to a trace.

To define your own trace using stored procedures

  1. Specify the events to capture using sp_trace_setevent.

  2. Specify any event filters. For more information, see Set a Trace Filter (Transact-SQL).

  3. Specify the destination for the captured event data using sp_trace_create.

For an example of using trace stored procedures, see Create a Trace (Transact-SQL).

To set trace definition defaults

SQL Server Profiler

To set trace display defaults

SQL Server Profiler

To create a trace

SQL Server Profiler


To add or remove events from a trace template

SQL Server Profiler