How to Find Running SQL Trace? – Interview Question of the Week #123

Question: How to Find Running SQL Trace? Once we know the trace exists, how to stop and delete them?

How to Find Running SQL Trace? - Interview Question of the Week #123 traceinterview

Answer: There are two questions in this blog post and both are very interesting one. SQL Trace are marked to be deprecated for a while and it is recommended to use extended events. In recent consulting engagement I was asked if I can help user to identify which all traces are running on SQL Server so they can identify and convert them to Extended Event and stop them afterwards.

Well, here is the script which can help you to identify the which traces are there on your server.

USE [master]
GOexSELECT T.id,
CASE T.status WHEN 0 THEN 'stopped' ELSE 'running' END AS [Status],
T.path,
CASE T.is_shutdown WHEN 0 THEN N'disabled' ELSE N'enabled' END AS [Is Shutdown?],
T.start_time,
T.stop_time
FROM sys.traces as T
WHERE T.is_default <> 1
GO

Here is the script which we can use to stop the trace and delete the definition from the server.

-- Stops the specified trace
EXEC sp_trace_setstatus @traceid = @id, @status = 0;
-- Closes the specified trace and deletes its definition
EXEC sp_trace_setstatus @traceid = @id, @status = 2;

Let me know if you find this blog post useful. Please leave your views in the comment section.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL Extended Events, SQL Scripts, SQL Server, SQL Trace
Previous Post
What is the Difference Between Physical and Logical Operation in SQL Server Execution Plan? – Interview Question of the Week #122
Next Post
How to Insert Results of Stored Procedure into a Temporary Table? – Interview Question of the Week #124

Related Posts

4 Comments. Leave new

  • Ramakrishna Reddy
    June 1, 2017 7:59 pm

    very very useful

    Reply
  • Hi Sir,

    exec sp_trace_setstatus @traceid = 2, @status = 0;
    exec sp_trace_setstatus @traceid = 2, @status = 2;

    After executing the first statement , the second statement shows the following message

    Msg 19059, Level 16, State 1, Procedure sp_trace_setstatus, Line 16
    Could not find the requested trace.

    please help me!!

    Reply

Leave a Reply