Reprinted with permission from owner. Original at http://nlsimmons.com/SQLScraps/

Using Profiler to run a trace can be an invaluable tool for troubleshooting performance problems with T-SQL code. Occasionally something happens to the connection between Profiler and the SQL Server where the trace is running and you can no longer manage the trace from the profiler window. Because an errant trace can impact performance, this situation needs to be rectified ASAP. To get information on the traces being run on a server, you can issue the following query.

SELECT * FROM ::fn_trace_getinfo(NULL)

The output is a table and looks like the following:

traceid property value
1 1 2
1 2 PathToFile
1 3 20
1 4 NULL
1 5 1

The [traceid] column of the result set is the ID of the trace. You’ll need to find the errant trace ID from this column. Check around with other folks that may be running traces in the event that the above query returns more than 2 traceid values. The default trace will always have traceid = 1. For more information on fn_trace_getinfo, see the TechNet article here.

Once you know the traceid of the errant trace, you can stop it and remove it from the server. To do that, you’ll need to use the sp_trace_setstatus stored procedure. The syntax is as follows:

EXEC sp_trace_setstatus @traceid = 2 , @status = 0
EXEC sp_trace_setstatus @traceid = 2 , @status = 2

The @status parameter is what is used by the stored procedure to start, stop, or remove the trace. A status of 0 indicates the ‘stop’ action is to be taken while a status of 2 indicates that the trace is to be removed. Using @status=1 starts the specified trace. Additional details regarding the sp_trace_setstatus procedure can be found here.

I hope this helps you in the event you need to quickly kill and remove an errant trace.

Leave a Reply

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