Blocking Alert on Azure SQL Db

Spread the love

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:-

  1. Create [sp_WhoIsActive] data capturing job using below script. Schedule it for every 5-10 minutes.
    https://github.com/imajaydwivedi/SQLDBA-SSMS-Solution/blob/master/Baselining/Job%20%5BDBA%20-%20Log_With_sp_WhoIsActive%5D.sql

  2. 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.
    https://github.com/imajaydwivedi/SQLDBA-SSMS-Solution/blob/master/Blocking%20Alert/Blocking-Alert-Azure.sql

That’s it. Post this, based on sp_whoisactive capturing frequency, and @p_BlockingThresholdTime_Minutes, recipients will receive mail similar to below screenshot:-

Happy Coding 

🙂

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
One Comment

Leave a Reply

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