Create Auto-Clearing Blocking Tree Alert on SQL Server

Spread the love

Recently one of my LinkedIn friends contacted me for a blocking alert that would work on on-prem & cloud SQL Server instances alike. Previously I wrote https://ajaydwivedi.com/2018/08/how-to-setup-blocking-alerts-on-sql-server blog post for on-prem SQL Server blocking alert which makes use of WMI based event.

So I wrote the following blocking alert setup code for SQL Server that has the below features –

  • Send mail notification to one or more recepients unlike SQL Agent job which is restricted to only one operator.
  • Parameter to control the consistent blocking threshold. Nobody wants spontaneous blockings which comes/goes.
  • Parameter to control the mail notification delay. Say, I want to be notified every X minutes (defult 15 minutes).
  • Parameter to control the mail notification subject.
  • Separate mail notification for Blocking & Script failure itself.
  • Auto-clearing feature. Means, if the blocking is resolved, we should get an automated mail notification saying Blocking is cleared.

Below are the required steps to setup blocking alert using mail notification:-

Step 01 -> Make sure a default global mail profile is set on SQL Server.

To achieve this, Expand SqlInstance node -> Management -> Right-click "Database Mail" -> Configure Database Mail -> Select option "Manage profile security" -> Check Public checkbox, and Select "Yes" for Default for profile that should be set a global default

Step 02 -> Baseline SQL Server using [sp_WhoIsActive] by creating a SQL Agent job (dba) Run-WhoIsActive. Schedule this job for every 2-5 minutes. Simply copy the below code, and add it in a SQL Agent Job Step.

Make sure to change the database from master to Your-DBA-Database. This script will create relevant tables like dbo.WhoIsActive and dbo.WhoIsActive_Staging. Ensure to update variable values for lines 8,9,14,15,18.

This job is designed to automatically generate a failure mail notification alert in case of @threshold_continous_failure value is reached. So no need to separately configure native job email notifications using operators.

https://github.com/imajaydwivedi/SQLDBA-SSMS-Solution/blob/master/Baselining/Baselining-With-WhoIsActive-CPU.sql

Below is a mail notification when the job failed 2 continuous times –

Baseline with sp_WhoIsActive SQL Agent Job Failure Notification

Below is a mail notification when the job was successful after a couple of failures –

Baseline with sp_WhoIsActive SQL Agent Job Failure Clearing Notification

Step 03 -> Create the stored procedure dbo.usp_get_blocking_alert. This stored procedure is the core of this blocking mail notification alert. Upon execution, it looks for consistent blocking based on @threshold_minutes parameter value, and sends email alert every @delay_minutes.

Step 04 ->Create a SQL agent job (dba) Alert-SdtBlocking. Schedule this job for every 1-2 minutes. Copy/paste below T-SQL code in the job step which runs under the context of Your-DBA-Database.

Ensure to update variable values on lines 43-45 & 48 from below code. In the sql agent job, it would be as below –

Parameter values to set for Blocking Alert in SQL Server

Similar to the step 02 job, this job also is built to generate failure mail notification along with auto-clearing features.

With the above SQL Agent job, our work for Blocking Mail Alert is complete.

Below is a sample blocking alert –

Below is a sample blocking auto clearing alert –

I hope this would be helpful to you DBAs out there.

Kindly like, subscribe & share if this alert helped you in any way.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.