2

A few months ago I added an extended event to track blocked processes and deadlocks, with a file output to track occurrences.

Every now and again, I check the log file, but find that the extended event has stopped, when I "Start Session" again through SSMS, it picks up again and logs as expected.

How can I ensure that this runs 24/7? Is it possibly failing somewhere and there's a log of errors that I'm unaware of?

Output File(s)

Selected Events

  • 1
    Is your session defined with STARTUP_STATE=ON so that it's automatically started when the SQL instance starts? – Dan Guzman Oct 2 '18 at 10:14
  • Ah, that's the problem, it was off and we have had a couple of maintenance restarts lately. Could you put that in an answer and I'll accept? Thanks. – William Oct 2 '18 at 10:37
4

It seems your Extended Event session is defined with STARTUP_STATE=OFF so it is not started automatically when SQL Server is restarted.

Change the session to STARTUP_STATE=ON to ensure it's always running (unless stopped manually):

ALTER EVENT SESSION [blocked_process] ON SERVER 
    WITH (STARTUP_STATE=ON);

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.