MS SQL Server - Trace Information and Stopping Traces

Server-side tracing is the process of having your SQL Server machine save events to a physical file on that machine without using the Profiler client tool. Server-side tracing is enabled and controlled by using SQL Server system-supplied stored procedures and functions. With these system-supplied processes, you can identify what to trace, when to start and stop tracing, what traces are running, and view trace information stored in the trace file.

Here is how you view the number of traces currently running:

SELECT count(*) FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

Here is how you can find more detail about the running traces:

SELECT * FROM :: fn_trace_getinfo(default)

You can terminate a trace with the 'sp_trace_setstatus' stored procedure using the traceid:

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

setting the status to 0 stops the trace setting the status to 2 closes the trace and deletes its definition from the server