Ajay Dwivedi sets up an alert:
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.
Click through for the instructions.