How to Stop & Delete a SQL Server Trace

There were a few times where I had to delete/close an existing SQL Server Trace and create a new one. It’s a 2-step process to completely remove a SQL Server Trace. First, you stop the trace. Second, you delete/close it.

sp_trace_setstatus takes 2 parameters, trace id and status.

The trace id is the id you find by doing a select * from sys.traces

The status is either a 0, 1 or 2:

0 stops the trace, 1 starts the trace, 2 closes the specified trace and deletes its definition from the server. (see MSDN image screenshot below)

(According to Microsoft, “A trace must be stopped first before it can be closed.”)

Let’s walk through the process.

First, do a select * from sys.traces to get the trace ID. Second, stop the trace by executing sp_trace_setstatus trace_id, 0 then, close/delete the trace by executing sp_trace_setstatus trace_id, 2. (see below screenshot for example)

Hope that helps!