sp_trace_setstatus

Transact-SQL Reference

Transact-SQL Reference

sp_trace_setstatus

Modifies the current state of the specified trace.

Syntax

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

Arguments

[ @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.

[ @status = ] status

Specifies the action to implement on the trace. status is int, with no default.

This table lists the status that may be specified.

Status Description
0 Stops the specified trace.
1 Starts the specified trace.
2 Closes the specified trace and deletes its definition from the server.

Note  A trace must be stopped first before it can be closed.

Return Code Values

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

Return code Description
0 No error.
1 Unknown error.
8 The specified Status is not valid.
9 The specified Trace Handle is not valid.
13 Out 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.

Remarks

sp_trace_setstatus is a Microsoft SQL Server 2000 stored procedure that performs many of the actions previously executed by xp_trace_* extended stored procedures available in earlier versions of SQL Server. Use sp_trace_setstatus instead of:

  • xp_trace_destroyqueue

  • xp_trace_pausequeue

  • xp_trace_restartqueue

  • xp_trace_startconsumer

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.

Permissions

Only members of the sysadmin fixed server role can execute sp_trace_setstatus.

Examples

See Also

fn_trace_geteventinfo

fn_trace_getfilterinfo

fn_trace_getinfo

sp_trace_generateevent

sp_trace_setevent

sp_trace_setfilter