Deadlock - SQL Server Agent Alert doesn''t work.

• Steve Velasco

  Old Hand

  Points: 332

  More actions

  August 30, 2005 at 4:59 pm

  #87459

  Hello everyone,

  I found articles about users having Alerts for Deadlock not working but haven't found any resolution. I have the same problem. I know my Alerts are working because I get notification from others except my Deadlock. I replicated deadlock but still nothing going with the Sql Server Agent Alert.

  My tracestatus (-1) shows:

  12041

  12051

  36021

  36041

  36051

  Here's my Alert script:

  EXECUTE msdb.dbo.sp_add_alert @name = N'Deadlocks 1',

  @message_id = 1205, @severity = 0, @enabled = 1, @delay_between_responses = 60,

  @include_event_description_in = 1,

  @event_description_keyword = N'lock',

  @category_name = N'[Uncategorized]'

  EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Deadlocks 1', @operator_name = N'velascos', @notification_method = 1

  I also see the error in the sqlserver logs but not in my eventlog as well.

  Thanks in advance.

  Steve

• gbn

  SSCertifiable

  Points: 6429

  More actions

  August 31, 2005 at 4:10 am

  #586396

  Remove the @event_description_keyword = N'lock' parameter.

  1. It's not needed, only one 1205 error

  2. It's a pattern match, that is LIKE not "=" so it would have to be '%lock%', and this will always be false here

  Cheers

  Shawn