Share via

  • Article

Using SQL Trace

The process of tracing with SQL Trace varies depending on whether you create and run your trace by using Microsoft SQL Server Profiler or by using system stored procedures.

As an alternative to SQL Server Profiler, you can use Transact-SQL system stored procedures to create and run traces. The process of tracing by using system stored procedures is as follows:

  1. Create a trace by using sp_trace_create.

  2. Add events with sp_trace_setevent.

  3. (Optional) Set a filter with sp_trace_setfilter.

  4. Start the trace with sp_trace_setstatus.

  5. Stop the trace with sp_trace_setstatus.

  6. Close the trace with sp_trace_setstatus.

    Note

    Using Transact-SQL system stored procedures creates a server-side trace, which guarantees that no events will be lost as long as there is space on the disk and no write errors occur. If the disk becomes full or the disk fails, the SQL Server instance continues to run, but tracing stops. If the c2 audit mode is set, and there is a write failure, tracing stops and the SQL Server instance shuts down. For more information about the c2 audit mode setting, see c2 audit mode Option.

In This Section

Topic

Description

Describing Events by Using Data Columns

Contains information about the data columns that are used to describe attributes of events.

Saving Trace Results

Contains information about saving trace results to a file or to a table.

Optimizing SQL Trace

Contains information about ways you can reduce the effects of tracing on system performance.

Filtering a Trace

Contains information about using filters for tracing.

Limiting Trace File and Table Sizes

Contains information about how to limit the size of files and tables where trace data is written. Note that only SQL Server Profiler can write trace information to tables.

Scheduling Traces

Contains information about how to set the start time and the end time for tracing.