Why is deadlock alert not working?

Question

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.

score 7 · Accepted Answer · edited Mar 18 '16 at 12:50

7

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

edited Mar 18 '16 at 12:50

Paul White

67,511
25
368
572

answered Mar 17 '16 at 11:38

spaghettidba

10,872
25
40

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 09 '21 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. – spaghettidba Feb 10 '21 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 '21 at 14:00

Why is deadlock alert not working?

1 Answers1

Remarks