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.
--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 Thursday, January 16, 2014 6:16 PM
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
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.
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.