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

EXEC msdb.dbo.sp_add_alert @name=N'1205 Deadlock', 

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.


1 Answer 1


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

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:


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
  • Is there any reason not to do this? For example, causing the ERRORLOG to grow? I want to be cautious and not break anything on production
    – Zikato
    Feb 9, 2021 at 15:15
  • Unless you have an inordinate amount of deadlocks happening, this won't make any difference on your ERRORLOG size. However, in modern versions of SQL Server, deadlock monitoring is more effectively performed using Extended Events rather than the plain old alert on error 1205. Feb 10, 2021 at 10:57
  • Sorry, I forgot to specify. I want this for a different error number. It should be infrequent but a process could go wrong.
    – Zikato
    Feb 10, 2021 at 14:00

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.