0

Some SQL Server Agent jobs failed recently with deadlock errors. I wanted to keep an eye on the issue and created a monitoring alert in SSMS. However, the event never fires.. which seems wrong.

Alert Properties:
alert properties

Alert scripted:

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock(s) detected . . .', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=720, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]', 
    @performance_condition=N'Locks|Number of Deadlocks/sec|Database|>|0', 
    @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Deadlock(s) detected . . .', 
    @operator_name=N'Watchdog', @notification_method = 1

What's missing? Or is there a better way to do this?

2
  • 1
    Do you know for sure that the alert never fires, or is it maybe just that the notification is not finding its way to where it should? Do you know, for sure, that notifications to the "Watchdog" operator work successfully?
    – Craig
    Commented Apr 19 at 3:53
  • Yes, I do because there is a History panel on the Alert Properties dialog that shows: Date of last alert: (Never occurred). Number of occurrences: 0.
    – wp78de
    Commented Apr 19 at 14:24

1 Answer 1

1

The error has to be written to the event log, which is what agent is polling. 1205 is by default not written to the event log (or errorlog). You can change this using sp_altermessage.

1
  • Nice. Thank you for pointing me in the right direct!
    – wp78de
    Commented Apr 19 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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