none
Deadlock Event Notifications

    Question

  • Hello,

    I've setup an Event Notification to fire into a Service Broker queue whenever a deadlock occurs in any database on my server. I then created a stored procedure that would be activated by the queue to email an alert to me.

    It's all working fine....but I am getting two emails for each deadlock when testing. I cannot work out why this is happening, I'll post the code below, could someone let me know what's going on please? Any help or advice would be most appreciated.

    Andrew

    --Create a service broker queue to hold the events
    CREATE QUEUE [DeadlockQueue];
    GO
    
    
    --Create a service broker service receive the events
    CREATE SERVICE [DeadlockService]
    ON QUEUE [DeadlockQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
    GO
    
    
    --Create the event notification for deadlock graphs on the service
    CREATE EVENT NOTIFICATION [CaptureDeadlocks]
    ON SERVER
    WITH FAN_IN
    FOR DEADLOCK_GRAPH
    TO SERVICE N'DeadlockService', N'current database';
    GO
    
    
    --Create procedure to email alert
    CREATE PROCEDURE [Alerting].[DeadlockNotification]
    AS
    EXEC [msdb].[dbo].sp_send_dbmail
    @profile_name = 'DBA Mail Profile',
    @recipients = 'Andrew@home.com',
    @subject = 'A deadlock has occurred'
    GO
    
    
    --Alert Service Broker Queue to run stored procedure
    ALTER QUEUE [dbo].[DeadlockQueue]
        WITH ACTIVATION
        ( STATUS = ON,
          PROCEDURE_NAME = [Alerting].[DeadlockNotification],
          MAX_QUEUE_READERS = 10,
          EXECUTE AS SELF
        );
    GO
    
    


    Thursday, January 16, 2014 6:15 PM

All replies

  • Hello Andrew,

    I do not think its an issue with SERVICE.

    This must be an issues with mail send. You may try by setting the "Account Retry Attempts" to 0 in you DB Mail Configuration wizard Or adding more delay. 

    • Proposed as answer by vr.babu Friday, January 17, 2014 9:37 AM
    Friday, January 17, 2014 5:10 AM
  • Thanks! I will have a try now.

    Andrew

    Friday, January 17, 2014 9:05 AM
  • Hi, have changed the retry attempts in the mail configuration to 0 and am still getting two emails.

    I ran a trace when running and I think the problem lies with queue being activated. Why would the queue be activated twice for this process?

    Andrew

    Friday, January 17, 2014 10:01 AM
  • try setting your max_queue_readers to '1'.

    also try deactivating activation on the queue...let messages build up and then query them directly w/ssms.

    see how many actual messages are being generated for a single deadlock.

    you might actually be getting two messages.

    this'll help you understand what is being generated.

    -mt


    mike t.

    Monday, January 20, 2014 9:13 PM
  • Hi Mike,

    Thank you for the help. I've deactivated the procedure on the queue and when I query it directly I can see only one entry per deadlock. I then set the maximum number of queue readers to 1 but still got two emails per deadlock when I enabled activation.

    Interestingly, I've set the stored procedure to log the deadlock details to a table before sending the mail and a blank row is being inserted (containing null values) each time. So it looks like the procedure is being fired twice, with the deadlock details the first time and with no details the second time.

    Andrew

    Tuesday, January 21, 2014 9:39 AM