create-and-run-traces-using-transact-sql-stored-procedures.md

title	Create and Run Traces Using Transact-SQL Stored Procedures | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql-server-2016
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	article
ms.assetid	80347417-338d-4bea-8885-91fae5181cfe
caps.latest.revision	8
author	JennieHubbard
ms.author	jhubbard
manager	jhubbard

Create and Run Traces Using Transact-SQL Stored Procedures

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

As an alternative to [!INCLUDEssSqlProfiler], you can use [!INCLUDEtsql] 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 [!INCLUDEtsql] 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 [!INCLUDEssNoVersion] instance continues to run, but tracing stops. If the c2 audit mode is set, and there is a write failure, tracing stops and the [!INCLUDEssNoVersion] 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 [!INCLUDEssSqlProfiler] 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)