Script : Setup email notification when blocking occurs
Posted by blakhani on November 12, 2014
How many times you have faced blocking situation and you are always late to take action.
Recently one of my friend asked if there is anyway to get automatic email when blocking appears in SQL Server. There is no default mechanism in SQL Server to send such emails. I have asked my friend to create a SQL Agent Job. Here are the information which can be queried and notified.
- How long blocking exists?
- Which are the queries/SPs creating blocking?
- What is the statement causing blocking.
What is needed to run below script?
- Database mail should be configured to send emails. In my example below I have a profile configured called “Database Mail” and I am sending mail to myself.
- Create Agent Job to run this script on periodic basis (5 min, 10 min or whatever you like)
/* Discialmer: The sample scripts are provided AS IS without warranty of any kind. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event I shall be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information,
or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation. */ SET NOCOUNT ON -- Checked for currenlty running queries by putting data in temp table SELECT s.session_id ,r.STATUS ,r.blocking_session_id ,r.wait_type ,wait_resource ,r.wait_time / (1000.0) 'WaitSec' ,r.cpu_time ,r.logical_reads ,r.reads ,r.writes ,r.total_elapsed_time / (1000.0) 'ElapsSec' ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1) AS statement_text ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text ,r.command ,s.login_name ,s.host_name ,s.program_name ,s.host_process_id ,s.last_request_end_time ,s.login_time ,r.open_transaction_count INTO #temp_requests FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id IF ( SELECT count(*) FROM #temp_requests WHERE blocking_session_id > 50 ) <> 0 BEGIN -- blocking found, sent email. DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<H1>Blocking Report</H1>' + N'<table border="1">' + N'<tr>' + N'<th>session_id</th>' + N'<th>Status</th>' + N'<th>blocking_session_id</th><th>wait_type</th><th>wait_resource</th>' + N'<th>WaitSec</th>' + N'<th>cpu_time</th>' + N'<th>logical_reads</th>' + N'<th>reads</th>' + N'<th>writes</th>' + N'<th>ElapsSec</th>' + N'<th>statement_text</th>' + N'<th>command_text</th>' + N'<th>command</th>' + N'<th>login_name</th>' + N'<th>host_name</th>' + N'<th>program_name</th>' + N'<th>host_process_id</th>' + N'<th>last_request_end_time</th>' + N'<th>login_time</th>' + N'<th>open_transaction_count</th>' + '</tr>' + CAST(( SELECT td = s.session_id ,'' ,td = r.STATUS ,'' ,td = r.blocking_session_id ,'' ,td = r.wait_type ,'' ,td = wait_resource ,'' ,td = r.wait_time / (1000.0) ,'' ,td = r.cpu_time ,'' ,td = r.logical_reads ,'' ,td = r.reads ,'' ,td = r.writes ,'' ,td = r.total_elapsed_time / (1000.0) ,'' ,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1) ,'' ,td = Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') ,'' ,td = r.command ,'' ,td = s.login_name ,'' ,td = s.host_name ,'' ,td = s.program_name ,'' ,td = s.host_process_id ,'' ,td = s.last_request_end_time ,'' ,td = s.login_time ,'' ,td = r.open_transaction_count FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID AND blocking_session_id > 0 ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id FOR XML PATH('tr') ,TYPE ) AS NVARCHAR(MAX)) + N'</table>'; EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML ,@body_format = 'HTML' ,@profile_name = N'Database Mail' ,@recipients = N'blakhani@outlook.com' ,@Subject = N'Blocking Detected' END DROP TABLE #temp_requests
Here is the sample email which I received when I caused blocking and executed the script.
if you want to use it, you need to create a SQL Agent Job which would run the script on periodic basics and send email.
Please modify as per your requirement/environment.
Help: SQL Server 
Sunder Singh Kapkoti said
Hi Balmukund,
thanks for the blog and information provided.
I want one help for TDE encryption related.. how we can set the expiry date for the TDE certificate, as it is giving the certificate expired.
blakhani said
How did you create certificate?
Sunder Singh kapkoti said
Hi,
we have used the below script to create TDE certificate for the database. now after 1 year when i am using the created certificate on other server to restore the database it is giving warning as “certificate is expired”
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘rewrrererBj$LP$HW4FkOZ$’
GO
CREATE CERTIFICATE test_cert
FROM FILE = ‘E:\CERTIFICATE_BACKUP\test_TDE_Certificate_File.cer’
WITH PRIVATE KEY (FILE = ‘E:\CERTIFICATE_BACKUP\test_TDE_Certificate_Key.pvk’,
DECRYPTION BY PASSWORD = ‘Wwewswse@$Zku\OQOZU6’);
GO
Please suggest
blakhani said
I think you need not worry. As per http://msdn.microsoft.com/en-us/library/bb934049.aspx
{
A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.
}
Sunder Singh Kapkoti said
Thanks a lot Balmukund……. that is really helpful.
blakhani said
Your welcome!
Aman said
Wonderful script. Thanks
blakhani said
thanks 🙂
Script : Set up Notification to get blocking tree via email in SQL Server « Help: SQL Server said
[…] one of my previous blog, I shared a script to generate email notification which would contain query output in HTML format. […]
Lee said
ON SQL 2012, this script can fire upon blocking event is detected.
Sai Gangadhar said
Hello Lakhani, it is an awesome script. how i can modify the script in order to get notification only when the blocking occurs more than 120 Secs
blakhani said
you can add filter like below
r.wait_time / (1000.0) > 120.0
Sangamesh said
Hi Balmukund,
Wonderful script.as you said above,I tried to filter it but getting below error.I have attached below my modified script as well.
Please help..
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ‘>’.
Msg 102, Level 15, State 1, Line 70
Incorrect syntax near ‘>’.
— Checked for currenlty running queries by putting data in temp table
SELECT s.session_id
,r.STATUS
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) > 120.0 ‘WaitSec’
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) > 120 ‘ElapsSec’
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN – 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END – r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(Object_schema_name(st.objectid, st.dbid)) + N’.’ + Quotename(Object_name(st.objectid, st.dbid)), ”) AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
INTO #temp_requests
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
IF (
SELECT count(*)
FROM #temp_requests
WHERE blocking_session_id > 50
) 0
BEGIN
— blocking found, sent email.
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML = N’Blocking Report’ + N” + N” + N’session_id’ + N’Status’ +
N’blocking_session_idwait_typewait_resource’ +
N’WaitSec’ + N’cpu_time’ +
N’logical_reads’ + N’reads’ +
N’writes’ + N’ElapsSec’ + N’statement_text’ + N’command_text’ +
N’command’ + N’login_name’ + N’host_name’ + N’program_name’ +
N’host_process_id’ + N’last_request_end_time’ + N’login_time’ +
N’open_transaction_count’ + ” + CAST((
SELECT td = s.session_id
,”
,td = r.STATUS
,”
,td = r.blocking_session_id
,”
,td = r.wait_type
,”
,td = wait_resource
,”
,td = r.wait_time / (1000.0) > 120.0
,”
,td = r.cpu_time
,”
,td = r.logical_reads
,”
,td = r.reads
,”
,td = r.writes
,”
,td = r.total_elapsed_time / (1000.0) > 120.0
,”
,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN – 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END – r.statement_start_offset
) / 2
) + 1)
,”
,td = Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(Object_schema_name(st.objectid, st.dbid)) +
N’.’ + Quotename(Object_name(st.objectid, st.dbid)), ”)
,”
,td = r.command
,”
,td = s.login_name
,”
,td = s.host_name
,”
,td = s.program_name
,”
,td = s.host_process_id
,”
,td = s.last_request_end_time
,”
,td = s.login_time
,”
,td = r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
AND blocking_session_id > 0
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
FOR XML PATH(‘tr’)
,TYPE
) AS NVARCHAR(MAX)) + N”;
EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
,@body_format = ‘HTML’
,@profile_name = N’SQLMail’
,@recipients = N’biradarsangamesh7@gmail.com’
,@Subject = N’Blocking Detected’
END
DROP TABLE #temp_requests
Nikitha said
The script is really helpful.Thank you
In scenarios like:When the block persist for more than 2 sec then the report has to be generated.
Now where should i modify in this script to get report for the above scenario.
blakhani said
2 second is a very short time. you can change the script and add extra filter.
Nikitha said
Thank You..
Can you please let me know how to add the login name of the blocking session id to the above script?
RK said
is there any way to set a trash hold. For example only fire up alert when blocking trash hold is more then 10 blockers ?
blakhani said
I think you meant threshold.
You can customize the script and modify below
IF (
SELECT count(*)
FROM #temp_requests
WHERE blocking_session_id > 50
) 0
to
IF (
SELECT count(*)
FROM #temp_requests
WHERE blocking_session_id > 50
) > 10
Muhammad Rizwan said
I created a job name BLOCKING ALERT JOB and pasted the above given script in the job step. After that I scheduled the Alert as follows
Name : Blocking Alert
Type : SQL Server Performance condition Alert
Object : General Statistics
Counter : Process blocked
Alert if counter : rises above
value ; 0
Now in RESPONSES tab, I checked EXECUTE JOB and selected my BLOCKING ALERT JOB.
The ALERT email is generated only when Process is blocked.
Suraj said
Are you Rizwan from Sydney
blakhani said
No.
caruncles said
I know this is an old post, but I just used it in the last few days. It is emailing me the blocking info just as you said. However, I was wondering if the Blocking_session_id could be identified by a user, program or even a workstationID?
I’m getting these in the middle of the night. And though I can SP_WHo2 the next morning, there is no way to know if the blocking spid is the same one which did the blocking the night before. If it was identified at the time the query ran, I would know exactly where to look for the blocker.
Thanx!
caruncles said
is there a way to identify the blocking_session_id ?
Basavaraja B S said
Thank you Dude
caruncles said
I have the script working and have received several emails when blocking occurs. I have it running in SQL Agent every 13 seconds. It only sends the email if it finds blocking. However, the SPID could change by the time I see the email. Could it retrieve a program name, procedure name, login ID or some other identifier?
blakhani said
yeah. you can get them from other DMVs
Praveen said
can we execute this script through windows task scheduler?
blakhani said
You can use SQLCMD to connect to SQL and run it.
Chintan said
is there a way we can add head blocker SPID column
Srinivas Medishetty said
Hi Balmukund Sir,
Thanks for providing script. It is very useful.
I want to know that without Job schedule in agent job, how can we get the alerts to our emails.
(Ex. If we configure job @ every 5 mins, every 10 mins etc, then MSDB will grow up fastly due to MSDB history table)