Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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?

  1. 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.
    1. 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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    24 Responses to “Script : Setup email notification when blocking occurs”

    1. 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!

    2. Aman said

      Wonderful script. Thanks

    3. […] one of my previous blog, I shared a script to generate email notification which would contain query output in HTML format. […]

    4. Lee said

      ON SQL 2012, this script can fire upon blocking event is detected.

    5. 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

    6. 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.

    7. 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.

    8. 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!

    9. caruncles said

      is there a way to identify the blocking_session_id ?

    10. 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?

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: