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.

Solarwinds

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)

Solarwinds
, , ,
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

Leave a Reply

Menu