Discover and Modify SQL Traces

How to list every running server trace and stop, start or delete one by id.

Published Mar 19, 2006

The three commands you need whenever a mystery trace is eating IO. fn_trace_getinfo(NULL) lists every server-side trace currently defined - its id, file path, status, start time and size limits. The sp_trace_setstatus calls then let you stop it (@status = 0), restart it (@status = 1) or delete it entirely (@status = 2).

SELECT * FROM ::fn_trace_getinfo(NULL)

EXEC sp_trace_setstatus @traceid = <TraceId>, @status = 0 --Stop
EXEC sp_trace_setstatus @traceid = <TraceId>, @status = 1 --Start
EXEC sp_trace_setstatus @traceid = <TraceId>, @status = 2 --Delete