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?

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)