Akeesoft

Akeesoft Logo

How to Configure Blocking Alerts with Automatic Email

automatic blocking email alert

To streamline the workflow of a SQL DBA, it’s important to proactively identify issues before they are reported by the end users or the clients. Setting up monitoring alerts allows the DBA to detect issues promptly. 

In this blog post, I’ll outline the steps to configure alerts for blocking queries in SQL Server.

To send out alerts from SQL Server, we can use DB Mail inbuilt feature of MS SQL Server.Begin by setting up the database mail feature. 

Launch SQL Server Management Studio, connect to your instance, and navigate to the Management folder. Right-click on Database Mail and choose the option to Configure Database Mail.

 

ssms database mail navigation

Once clicked on Configure Database Mail, Database Mail wizard will come up like below

 

 

db mail wizard

Select Set up Database Mail by performing following task.

 

choose task

If you are configuring database mail in sql server for the first time,Will get alert to enabled the DB Mail feature like below

 

db mail enable

Click on Yes , to enable DB Mail Feature , to get the Window to create mail profile name,Enter the profile name as below and click add button to add SMTP details.

 

db mail profile

Click on add ,you will get below window to enter all details related to SMTP details.,Like Account Name for identification,SMTP Email Address,Display Name,Server Name , Port Number,Choose authentication and enter user name and password.

All the SMTP details can get from the hosting provider.

 

smtp details

Click on Next ,you will see below windows with all the required details to create a DB mail Profile.

 

Click on Next ,You will get a profile security window,leave all as default and click on Next.

 

db mail profile

Click on Next, will see system parameters window,leave as default and click on next.

 

db system parameters

Click on Next , Now we are at the Database Mail Completion Wizard summary.

 

wizard completion

Click on Finish, DB Mail profile will be create.

Once the DB mail profile created,need to test whether DB mail working as expected.

Navigate to Send Test-Email and click.

 

test email
test email

Once To email is given and Send Test E-Mail, if we receive mail like the below, means the DB Mail configuration is marked as successful.

 

test email success

Now its time to configure an alert for Blockings


To create this alert, we will use SQL Agent.

Navigate to New Job as below 

 

new job

To create this alert, we will use SQL Agent.

Navigate to New Job as below 

 

Once Click on Add Step will see the below window to enter step name and query to execute

Then click on parse to check if any syntax error , below is the script used to alert the blocking queries

 

 

SET NOCOUNT ON
DECLARE @xml nvarchar(max)
SELECT @xml = Cast((SELECT @@SERVICENAME AS 'td','',b.session_id AS 'td',
'',
(b.wait_duration_ms/1000)/60 AS 'td',
'',
b.wait_type AS 'td',
'',
b.blocking_session_id AS 'td',
'',
t.text AS 'td'
FROM sys.dm_os_waiting_tasks b inner join sys.dm_exec_requests r on r.session_id= b.session_id
OUTER APPLY
sys.dm_exec_sql_text(sql_handle) t
WHERE b.blocking_session_id <> 0 and b.wait_duration_ms>180000
FOR xml path('tr'), elements) AS NVARCHAR(max))
Declare @body nvarchar(max)
SET @body =
'<html>
<head>
<style>
table, th, td
{
border: 1px solid black;
border-collapse: collapse;
text-align: center;
}
</style>
</head>
<body>
<H2>
Blocking queries
</H2>
<table>
<tr>
<th>Instance Name</th><th> Blocked Session ID </th> <th> Wating in minutes </th> <th> Wait type</th>
<th> Blocking Session ID</th><th>Query waiting to execute</th>
</tr>'
SET @body = @body + @xml + '
</table>
</body>
</html>'
if(@xml is not null)
BEGIN
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Akeesoft_dbmail',
@body = @body,
@body_format ='html',
@recipients = 'youremail_id@email.com',
@subject = 'Blocking Queries Alert | Validate';
END
SET NOCOUNT OFF
new job

Now it is time to create a schedule to run this script every 10 minutes, so that script will check if any blockings .Can change to 15 minutes or 20 minutes as per the requirement.

Below the snap of the schedule selected .

 

schedule

Job is created and started manually and successful.

 

job success

In conclusion, this is a detailed step-by-step explanation of creating a DB mail and create an alert for blocking happening on SQL Server.

This is not a fixed way and other ways of achieving also possible, but this can be the basic way.

I hope this is helpful for you if you are looking to configure blocking alerts with automatic email.

Blocking Alert