How to Configure Blocking Alerts with Automatic Email
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.
Once clicked on Configure Database Mail, Database Mail wizard will come up like below
Select Set up Database Mail by performing following 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
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.
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.
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.
Click on Next, will see system parameters window,leave as default and click on next.
Click on Next , Now we are at the Database Mail Completion Wizard summary.
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.
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.
Now its time to configure an alert for Blockings
To create this alert, we will use SQL Agent.
Navigate to New Job as below
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
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 .
Job is created and started manually and successful.
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.
