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';
@message_id = 1205,
@parameter = 'WITH_LOG',
@parameter_value = 'true';
I generated the next deadlock and the 1205 is now in my Error Log:
No comments:
Post a Comment