I'm running an SQL trace by calling SQL Server Profiler's system stored procedures directly. I tried using sp_trace_setstatus to stop the trace, but it didn't work. The trace running on the server still shows up in fn_trace_getinfo()'s output. How can I stop the trace?
SQL Server Books Online (BOL) documents most things well enough but does a poor job documenting Profiler and its related system stored procedures and functions. Let's walk through a simple example to demonstrate the problem you're having. Imagine that you've successfully started a trace that has a trace ID of 1. The following command, which lists all trace definitions on the server, shows that your trace is running:
SELECT traceid,property,value FROM ::fn_trace_getinfo(0) WHERE property = 5
BOL says that property 5 shows the current status of the trace. BOL doesn't enumerate property 5's status values anywhere, but simple tests show that a 0 value means that the trace is defined on the server but isn't running and a 1 value denotes that the trace is actively running and capturing events.
The entry for sp_trace_setstatus in BOL says that you must stop a trace before you can close it. However, BOL's explanation isn't particularly clear. Assuming that your trace ID is still equal to 1, the following commands, run in this order, stop the trace and delete the definition from the server:
-- This command stops the trace and must be -- run first. EXEC sp_trace_setstatus 1 ,0 -- This command deletes the trace from the server. EXEC sp_trace_setstatus 1 ,2
BOL also fails to note that you won't get an error message if you run the EXEC sp_trace_setstatus 1 ,2 command before stopping the trace. The trace will remain active if you don't run both commands in the correct order. The symptoms you describe suggest that you're stopping the trace but not closing it.
You might need to stop a trace, then restart it later. Instead of deleting the trace, you can stop the trace with the previous command, then restart the trace by using the following command:
-- This command restarts the trace with trace ID -- = 1. EXEC sp_trace_setstatus 1 ,1
Also note that a trace doesn't write data to the output file in realtime; SQL Server batches up trace data and writes it out in chunks. In addition, SQL Server doesn't automatically write out the data when you stop the trace; you first must close the trace. The smallest chunk of data I've ever seen written out was 128K, so a small trace file might not write the data to disk until you've stopped and closed the trace by using the two-command process outlined earlier. Of course, you can stop and close a trace by stopping SQL Server.
Learning how the procedures associated with running an SQL trace work is tricky because they aren't documented well. However, you can streamline the learning process by using one instance of Profiler to watch the commands that another instance of Profiler sends to the server to manage its traces.
