Introducing SQL Trace

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

Topic

Description

SQL Trace Terminology

Contains a glossary of terms used to discuss SQL Trace.

SQL Trace Architecture

Contains a diagram that illustrates how SQL Trace works in SQL Server.

Using SQL Trace

Contains information about using SQL Trace. For example, the topics in this section describe how to create trace filters and saving a trace.

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

Stored procedure

Task performed

fn_trace_geteventinfo (Transact-SQL)

Returns information about events included in a trace.

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.

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 How to: 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 How to: 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

Transact-SQL

To add or remove events from a trace template

SQL Server Profiler

Transact-SQL