How to find if there is a trace running in the server

What is Server-side tracing?
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 in the system and the details of the trace:

Number of traces(active and stopped) currently in the system:
select * from sys.traces

Here is how you view the active(trace is running) traces in the system:

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

You can stop and terminate a trace by executing the following:

EXEC sp_trace_setstatus @traceid , @status = 0 — Stops the trace
EXEC sp_trace_setstatus @traceid , @status = 2 — Eliminates the definition of the trace from the server.



Tagged: , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: