Skip to content

sp-trace-setstatus-transact-sql.md

Latest commit

 

History

History
93 lines (67 loc) · 3.32 KB

sp-trace-setstatus-transact-sql.md

File metadata and controls

93 lines (67 loc) · 3.32 KB
title sp_trace_setstatus (Transact-SQL)
description Modifies the current state of the specified trace.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sp_trace_setstatus_TSQL
sp_trace_setstatus
helpviewer_keywords
sp_trace_setstatus
dev_langs
TSQL

sp_trace_setstatus (Transact-SQL)

[!INCLUDE SQL Server]

Modifies the current state of the specified trace.

Important

[!INCLUDE ssNoteDepFutureAvoid] Use Extended Events instead.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

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

Arguments

[!INCLUDE extended-stored-procedures]

[ @traceid = ] traceid

The ID of the trace to be modified. @traceid is int, with no default. The user employs this @traceid value to identify, modify, and control the trace. For information about retrieving the @traceid, see sys.fn_trace_getinfo.

[ @status = ] status

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

The following table lists the status that can 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. A trace must be stopped and closed first before it can be viewed.

Return code values

The following table describes the code values that you could get, following completion of the stored procedure.

Return code Description
0 No error.
1 Unknown error.
8 The specified Status isn't valid.
9 The specified Trace Handle isn't valid.
13 Out of memory. Returned when there isn't enough memory to perform the specified action.

If the trace is already in the state specified, [!INCLUDE ssNoVersion] returns 0.

Remarks

Parameters of all SQL Trace stored procedures (sp_trace_*) are strictly typed. If these parameters aren't called with the correct input parameter data types, as specified in the argument description, the stored procedure returns an error.

For an example of using trace stored procedures, see Create a Trace.

Permissions

Requires ALTER TRACE permission.

Related content