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

Posted in Blocking, Script, SQL Server | Tagged: , , , , , | 2 Comments »

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
  • Posted in Script, SQL Server | Tagged: , , , , , | 9 Comments »

     
    Follow

    Get every new post delivered to your Inbox.

    Join 865 other followers