How to Find Running SQL Trace? – Interview Question of the Week #123

Interview question: How do you find running SQL Traces, then stop and close a selected trace?

Answer: Inspect sys.traces to identify the trace ID and status. For an approved retirement, call sp_trace_setstatus with status 0 to stop that trace, then status 2 to close it and remove its definition. Check the ID carefully before either change.

In a consulting engagement, a customer wanted to find their remaining SQL Traces so they could move the useful monitoring to Extended Events. That is a sensible inventory task: SQL Trace is deprecated, but an existing instance may still have traces worth reviewing before they are removed.

Start with a read-only inventory. This intentionally excludes the default trace so you can focus on traces someone added:

SELECT t.id AS trace_id,
       CASE t.status WHEN 1 THEN N'running'
                     WHEN 0 THEN N'stopped'
                     ELSE N'unknown' END AS trace_status,
       t.path,
       t.is_shutdown,
       t.start_time,
       t.stop_time
FROM sys.traces AS t
WHERE t.is_default = 0
ORDER BY t.id;

The original published code had GOexSELECT where the query should begin. That typo is removed here. Review each trace’s purpose, owner, event selection, and output before stopping it. A file path can be null for a rowset trace.

When you have confirmed a specific trace can be retired, set its ID deliberately in the following script. The default NULL prevents an accidental change if you paste the example unchanged:

DECLARE @trace_id int = NULL; -- replace only after reviewing the trace

IF @trace_id IS NULL
    THROW 50000, 'Set and verify the trace ID first.', 1;

EXEC sys.sp_trace_setstatus @trace_id, 0; -- stop
EXEC sys.sp_trace_setstatus @trace_id, 2; -- close and remove definition

SELECT id, status, path
FROM sys.traces
WHERE id = @trace_id;

The last query should return no row after a successful close. Keep any required trace-file evidence according to your retention policy; closing a trace removes its SQL Server definition, not a decision about archived evidence. These commands require ALTER TRACE.

For new monitoring, use Extended Events. The trace inventory is still useful during a careful migration, even though SQL Trace should not be the starting point for a new capture.

Microsoft’s sys.traces reference and sp_trace_setstatus reference document the states and retirement sequence.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.