I noticed a Server had neither the Traceflag 1222 nor 1204 set to on. I think turning these on is a good thing. Is there any reason not to? Perhaps there is some alternate means that deadlock information is being collected. I'm not sure which is better between Trace Flag 1222 and 1204 - perhaps turn on both.
These flags will enable the deadlock information to be logged in the SQL Server log. A better option is to build an xEvent deadlock capture that uses an event file target.
Thankyou. I'll look more into monitoring Deadlocks via Extended Events.
... I noticed in system_health, the deadlock event is selected. But when I query this, I get only 2 hours worth, mostly flooded with these:
- connectivity_ring_buffer_recorded
- security_error_ring_buffer_recorded
So if there was a Deadlock yesterday, it wouldn't appear. Maybe this is just the default event session
So perhaps we need to set up a new event session just for Deadlocks (just like your link shows how to do).
I prefer setting up the session - that way I have the information in a file for as long as I want to keep it.