sp_trace_setstatus (Transact-SQL)


THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Modifies the current state of the specified trace.

System_CAPS_ICON_important.jpg Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status  

[ @traceid= ] trace_id
Is the ID of the trace to be modified. trace_id is int, with no default. The user employs this trace_id value to identify, modify, and control the trace. For information about retrieving the trace_id, see sys.fn_trace_getinfo (Transact-SQL).

[ @status= ] status
Specifies the action to implement on the trace. status is int, with no default.

The following table lists the status that may be specified.

0Stops the specified trace.
1Starts the specified trace.
2Closes the specified trace and deletes its definition from the server.
System_CAPS_ICON_note.jpg Note

A trace must be stopped first before it can be closed. A trace must be stopped and closed first before it can be viewed.

The following table describes the code values that users may get following completion of the stored procedure.

Return codeDescription
0No error.
1Unknown error.
8The specified Status is not valid.
9The specified Trace Handle is not valid.
13Out of memory. Returned when there is not enough memory to perform the specified action.

If the trace is already in the state specified, SQL Server will return 0.

Parameters of all SQL Trace stored procedures (sp_trace_xx) are strictly typed. If these parameters are not called with the correct input parameter data types, as specified in the argument description, the stored procedure will return an error.

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

User must have ALTER TRACE permission.

sys.fn_trace_geteventinfo (Transact-SQL)
sys.fn_trace_getfilterinfo (Transact-SQL)
sp_trace_generateevent (Transact-SQL)
sp_trace_setevent (Transact-SQL)
sp_trace_setfilter (Transact-SQL)
SQL Trace

Community Additions