Recently one of my LinkedIn friend contacted me for blocking alert that would work on Azure SQL Db as my previous blog post on Blocking Alert used WMI based alerting, and did not work on Azure.
In order to establish the same, below are the 2 steps one can take:-
- Create [sp_WhoIsActive] data capturing job using below script. Schedule it for every 5-10 minutes.
- Create another job with code from below script. Do modify @p_recipients & @p_BlockingThresholdTime_Minutes variable values before you run the script. Remove my email id ‘[email protected]’ from bottom code.
That’s it. Post this, based on sp_whoisactive capturing frequency, and @p_BlockingThresholdTime_Minutes, recipients will receive mail similar to below screenshot:-