Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Script : Set up Notification to get blocking tree via email in SQL Server

Posted by blakhani on November 18, 2014


In one of my previous blog, I shared a script to generate email notification which would contain query output in HTML format. The query was to find currently running processes in SQL Server. There was a problem with that script. The script only deals with currently “executing” query. So if a query has an open transaction and waiting for either commit or rollback then it would not be shown in email.

Due to that I wrote another script by getting idea from Ajith Krishnan’s (FaceBook ) script which would also show the open transaction making the life little more easier. Here is the email notification

In above example there are two blocking chains in SQL Server. Chain # 1 is where 72 is blocked by 68 and 68 is blocked by 59. In second chain, 71 is blocked due to 75.

Here is the script to get such emails. There are some initial settings required.

  1. Configure Database Mail
  2. Modify script to change the profile name and email address (else I will receive email for blocking alerts on your server)
  3. 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.
*/


SET nocount ON; 
SET concat_null_yields_null OFF 

go 

WITH blockers (spid, blocked, level, batch, lastwaittype,hostname,cmd,dbid,loginname,open_tran,login_time) 
     AS (SELECT spid, 
                blocked, 
                Cast (Replicate ('0', 4-Len (Cast (spid AS VARCHAR))) 
                      + Cast (spid AS VARCHAR) AS VARCHAR (1000))         AS 
                LEVEL, 
                Replace (Replace (T.text, Char(10), ' '), Char (13), ' ') AS 
                BATCH, 
                R.lastwaittype,
                R.hostname,r.cmd,r.dbid,r.loginame,r.open_tran,r.login_time
         FROM   sys.sysprocesses R WITH (nolock) 
                CROSS apply sys.Dm_exec_sql_text(R.sql_handle) T 
         WHERE  ( blocked = 0 
                   OR blocked = spid ) 
                AND EXISTS (SELECT spid, 
                                   blocked, 
                                   Cast (Replicate ('0', 4-Len (Cast (spid AS 
                                         VARCHAR 
                                         ))) 
                                         + Cast (spid AS VARCHAR) AS VARCHAR ( 
                                         1000)) 
                                   AS 
                                       LEVEL, 
                                   blocked, 
                                   Replace (Replace (T.text, Char(10), ' '), 
                                   Char (13 
                                   ), 
                                   ' ') AS 
                                       BATCH, 
                                   R.lastwaittype, 
                                    R.hostname,r.cmd,r.dbid,r.loginame,r.open_tran,r.login_time
                            FROM   sys.sysprocesses R2 WITH (nolock) 
                                   CROSS apply 
                                   sys.Dm_exec_sql_text(R.sql_handle) T 
                            WHERE  R2.blocked = R.spid 
                                   AND R2.blocked <> R2.spid) 
         UNION ALL 
         SELECT R.spid, 
                R.blocked, 
                Cast (blockers.level 
                      + RIGHT (Cast ((1000 + R.spid) AS VARCHAR (100)), 4) AS 
                      VARCHAR 
                      ( 
                      1000)) AS 
                LEVEL, 
                Replace (Replace (T.text, Char(10), ' '), Char (13), ' ') 
                AS BATCH, 
                R.lastwaittype,
                R.hostname ,r.cmd,r.dbid,r.loginame,r.open_tran,r.login_time
         FROM   sys.sysprocesses AS R WITH (nolock) 
                CROSS apply sys.Dm_exec_sql_text(R.sql_handle) T 
                INNER JOIN blockers 
                        ON R.blocked = blockers.spid 
         WHERE  R.blocked > 0 
                AND R.blocked <> R.spid) 
SELECT N'' 
       + Replicate (N'|.......', Len (level)/4 - 2) 
       + CASE WHEN (Len (level)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + 
       Cast ( 
       spid AS VARCHAR (10)) + ' ' + batch AS BLOCKING_TREE, 
       hostname, 
       lastwaittype, cmd,dbid,loginname,open_tran,login_time,
       Getdate()                           AS 'RunTime' ,
       level
INTO #BLOCKERS
FROM   blockers WITH (nolock) 
ORDER  BY level ASC 

    --Select BLOCKING_TREE,hostname,lastwaittype,time,cmd,dbid,loginname,open_tran from   #BLOCKERS
    --order by level asc

    DECLARE @tableHTML NVARCHAR(MAX);

    SET @tableHTML = N'<H1>Blocking Tree Report</H1>' + N'<table border="1">' + N'<tr>' + 
    N'<th>Blocking_Tree</th>' + N'<th>hostname</th>' + N'<th>lastwaittype</th>'+'<th>CurrentTime</th>' 
    + N'<th>cmd</th>' 
    + N'<th>dbid</th>' 
    + N'<th>loginname</th>' 
    + N'<th>open_tran</th>' 
    + N'<th>login_time</th>' 
    + '</tr>' + CAST((
SELECT td = Blocking_Tree,'',
         td =hostname,'',
         td =lastwaittype,'',
         td =RunTime,'',
         td= cmd,'',
         td= dbid,'',
         td= loginname,'',
         td=open_tran,'',
         td=login_time,''
         FROM #BLOCKERS
             order by level asc
FOR XML PATH('tr')
                    ,TYPE
                ) AS NVARCHAR(MAX)) + N'</table>';   

If (select count(*) from #BLOCKERS) > 1
begin

    EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
        ,@body_format = 'HTML'
        ,@profile_name = N'Database Mail'
        ,@recipients = N'blakhani@outlook.com'
        ,@Subject = N'Blocking Tree Report'        
end

drop table #BLOCKERS

Hope this helps.

Cheers,

Balmukund

Advertisements

7 Responses to “Script : Set up Notification to get blocking tree via email in SQL Server”

  1. Kumar said

    We have purchased SQL server 2014 edition for a particular project. Now one more project required SQL 2008 or above versions in a different server. Can in download the SQL 2008 version available in the Microsoft VLSC and use it in server..Note that i have to use the SQL server 2014 edition in another server….

  2. Its Really cool Script…..and its very much useful thanks for Script….

  3. Amit Kumar said

    Nice script 🙂

  4. Robb Hill said

    It would be really nice to have this check for how long the block has been running before sending the alert…otherwise the results you get from the agent are somewhat random. A block could happen but doesnt really indicate an issue if it was gone in a few seconds.

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: