Wednesday, September 18, 2024

Where is the 1205 error for the SQL Server Deadlock ?!!

Just a short post, but I believe still helpful.  Yesterday I was playing around with different methods of capturing deadlock notifications.  I used XE's (Extended Event Sessions), Service Broker Queue / Event Notifications and SQL Server Alerts. While testing the Alerts, I simulated my own deadlock and produced the deadlock event myself, like this:



The expectation here was that the SQL Server Alert would send me a notification for the deadlock event... but it didn't.  Curious.  I verified the mail and the operator, I verified the last_occurrence_date of the alert... everything checked out good, but there still wasn't any 1205 recorded in the SQL Server Error log.  Then I just decided to be sure the 1205 was in sys.messages:







We can see it IS there, but do you see that "is_event_logged" = 0 ?  This means it is not going to be recorded in the SQL Server Error log.  Easily amended:

               EXEC master.sys.sp_altermessage
                  @message_id = 1205,
                  @parameter = 'WITH_LOG',
                  @parameter_value = 'true';

I generated the next deadlock and the 1205 is now in my Error Log:



All is well now, and I can move forward w/the deadlock capture.  I will post that here when it is complete.  See you soon!





No comments:

Post a Comment