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)

Status for sp_trace_setstatus(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)

sp_trace_setstatus screenshot

Hope that helps!

One Reply to “How to Stop & Delete a SQL Server Trace”

Leave a Reply

Your email address will not be published. Required fields are marked *