1

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<>1
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=<trace_id>, @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=<trace_id>, @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.

1
  • RFL on our servers every one use SQL Server Profiler for tracing purpose and I tried many scenarios through profiler to generate such entries forcefully but failed
    – AA.SC
    Jul 16 '14 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.