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