Muokkaa

Jaa

Create and Run Traces Using Transact-SQL Stored Procedures

  • Artikkeli

Applies to: SQL Server

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 Server Configuration Option.

In This Section

Topic Description
Optimize SQL Trace Contains information about ways you can reduce the effects of tracing on system performance.
Filter a Trace Contains information about using filters for tracing.
Limit 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.
Schedule Traces Contains information about how to set the start time and the end time for tracing.

See Also

sp_trace_create (Transact-SQL)
sp_trace_setevent (Transact-SQL)
sp_trace_setfilter (Transact-SQL)
sp_trace_setstatus (Transact-SQL)