3

I created a deadlock alert including notification to an operator like this:

EXEC msdb.dbo.sp_add_alert @name=N'1205 Deadlock', 
        @message_id=1205, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=30, 
        @include_event_description_in=1, 
        @database_name=N'myDB', 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000';

This alert is registered for notification.

It's history says it never appeared although it is in place for months and there are multiple deadlocks per day. I created a deadlock manually. It shows up in the Log File.

What is missing for this alert to raise a notification? Other notifications like e.g. 18456 Invalid Login work fine.

6

Probably the error 1205 is not set up to be logged in ERRORLOG. Look it up in sys.messages:

SELECT *
FROM sys.messages 
WHERE message_id = 1205;

If the column is_event_logged is set to 0, change it this way:

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

The documentation for sp_add_alert describes under which conditions an alert is raised:

Remarks

sp_add_alert must be run from the msdb database.

These are the circumstances under which errors/messages generated by SQL Server and SQL Server applications are sent to the Windows application log and can therefore raise alerts:

  • Severity 19 or higher sys.messages errors
  • Any RAISERROR statement invoked with WITH LOG syntax
  • Any sys.messages error modified or created using sp_altermessage
  • Any event logged using xp_logevent

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.