Send Email When the Blocked Session Count Exceeds a Certain Number in SQL Server

In some cases, sessions are blocked in databases. There are many reasons for blocking. In this article, I will not go into details of this topic.

In this article, I will explain how to send mail when the blocked session count in a database exceeds a certain number. Because if we do not know about this situation, the application will be locked and will not work. So we need to create a warning mechanism to intervene before the application becomes inaccessible.

We have written a script for this process as follows. I want to briefly explain what is happening in this script.

It checks the blocked session count in the instance you are working in every minute. If the blocked session count is greater than the number we have specified, it sends it to the mail address specified in the script by using the Database Mail profile specified in the script. If you did not configure database mail before, you may want to read the article named “How To Configure Database Mail On SQL Server

Script To Send Email When the Blocked Session Count Exceeds a Certain Number

You need to change the following parameters for the Script to work properly.

@your_email_address ==> The e-mail address to which you would like to send an email about Lock

@lockcount                     ==> If the blocked session count exceeds this value, an email will be sent to the above email address. (default value is 100)

Faruk Erdem
Author: Faruk Erdem

Leave a Reply

Your email address will not be published. Required fields are marked *