Sql Server sys.Traces log Working with traces on production server I found some mysterious entries to me in sys.Traces, When I run below query it shows me status of all traces as ‘Paused’ along with entries in sys.dm_exec_sessions, which scenarios generate these types of records.

SELECT t.id  
 ,CASE t.status  
 WHEN 0 THEN 'Paused'  
 ELSE 'Running'  
 END AS status  
 ,t.reader_spid
 ,t.start_time
 ,s.HOST_NAME
 ,s.program_name
 ,s.login_name
 ,t.*
FROM sys.traces AS t
 LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id = reader_spid
WHERE t.is_rowset = 1 
and is_default&lt;&gt;1
If the following command is run by someone with the authority to do so, the trace will be stopped.  

exec sp_trace_setstatus @traceid=&lt;trace_id&gt;, @status=0 -- Stop the trace


However it still exists in the stopped state.  This is the state you are reporting as 'Paused'.  If the stopped trace should be ended, then a second command is needed:

exec sp_trace_setstatus @traceid=&lt;trace_id&gt;, @status=2 -- Close the trace


This removes the stopped trace from the server.  Once the trace is stopped and closed you can read the full trace.

Of course, if the trace is to be periodically restarted, then you can leave it in the stopped state until the next activation.