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

• Edited by Andrew Pruski 17 hours 13 minutes ago

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 1 hour 52 minutes ago

Thanks! I will have a try now.

Andrew

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