Suppose you want to or need to know anytime your SQL Server is experiencing blocking where queries are being blocked and it may or may not escalate to a deadlock situation. Chances are you might be using a comprehensive tool to get performance alerts. But I don't always have access to such tools at the clients.
So here I have a SQL Server Agent alert to get automatic email when SQL Server detects queries being blocked.
There are basically two key alert settings I am using:
1. The minimum threshold value for sending the email alert is 4. That means if more than 4 processes are involved in blocking an email is sent out immediately.
2. The alert email shows what the current number of process being blocked is (I highlighted it in yellow in below alert). That way we can assess how quickly or slowly the blocking is getting worse.
After that first email alert, if the situation does not resolve itself, it will keep sending the email every 15 minutes with the updated count of processes being blocked.
So if we do not get a second email alert in next 15 minutes, it would mean it resolved itself and, no manual intervention is needed.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert@name=N'Alert on SQL Server SPIDs being blocked',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=900, -- This value is in seconds so 900 seconds = every 15 minutes
@include_event_description_in=1,
@notification_message=N'Pleae contact your DBA team at DBA-Team@YourOrganization.org for further assistance.',
@category_name=N'[Uncategorized]',
@performance_condition=N'General Statistics|Processes blocked||>|3',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification@alert_name=N'Alert on SQL Server SPIDs being blocked', @operator_name=N'App_Team', @notification_method = 1
GO
EXEC msdb.dbo.sp_update_notification@alert_name=N'Alert on SQL Server SPIDs being blocked', @operator_name=N'DBA', @notification_method = 1
GO
EXEC msdb.dbo.sp_update_notification@alert_name=N'Alert on SQL Server SPIDs being blocked', @operator_name=N'Pager Email', @notification_method = 1
GO
GO