Motherofallgeeks

I wanted to share a little something we put together to monitor blocking on our SQL 2005 servers. Every once in awhile the blocking gets out of control and when it does we get these emails so we can quickly jump in and troubleshoot before the Help desk tickets start rolling in.  Create this in your master database be sure to change the database name where it says YOURDB. Create a SQL Server job that runs constantly. In the Job SQL put the following infinite loop to call the proc below:

declare @count int
set @count = 5000000
WHIle @count > 0
BEGIN
waitfor delay ’00:00:15′
exec [Proc_LeadBlocker] 0, ‘YOUR MAIL PROFILE’, ‘Youremail@email.com’
end

CREATE  PROCEDURE [dbo].[Proc_LeadBlocker]
(@maxtime int = 0,@MailProfile varchar(100),@recipients varchar(255) )   
as   
--=======================================================================   
-- Proc Name : sp__leadblocker   
-- Description : This procedure will report processes that are blocking   
--    other processes and processes that are being blocked   
--    
-- Author : Motherofallgeeks.com
--=======================================================================   
 
--DECLARE VARIABLES   
DECLARE @BLKemailmsg varchar(1000)   
DECLARE @WAITemailmsg varchar(1000)   
DECLARE @message varchar(max)
   
--CHECK FOR BLOCKING   
IF EXISTS (select 1 FROM sys.dm_exec_requests  
     WHERE Session_ID in
      (SELECT blocking_session_id 
      FROM sys.dm_exec_requests 
      WHERE (wait_time/15000) > @maxtime))    
BEGIN  
 --BUILD BLOCKING PROCESSES EMAIL MSG   
 select @BLKemailmsg = 'The Following Processes are Blocking other Processes:(BLOCKER)  <br>' 
 select @BLKemailmsg = @BLKemailmsg+    
   '<b> SPID: </b>'+ convert(varchar(5),DR.Session_ID) +  '<br>' +
   '<b> STATUS: </b>' + DS.status +    '<br>' +
   ' <b>CMD: </b>' +DR.command+    '<br>' +
   '<b> DB:</b> '+ db_name(DR.database_id) +    '<br>' +
   '<b> LOGIN:</b> '+ DS.login_name +    '<br>' +
   '<b> HOSTNAME:</b> '+ DS.host_name +    '<br>' +
   '<b> LAST_BATCH_TIME: </b>'+ convert(varchar(20),Ds.last_request_Start_time,113)+   '<br>' +
 '<b> wAIT rESOURCE: </b>'+wait_resource + '<br>' +
 '<b> last_wait_type: </b>'+last_wait_type + '<br>' +
   ' <b> SQL Statement: </b>' +  (select name from [YOURDB].sys.objects where object_id=s2.objectid)+ ' '+ SUBSTRING(s2.text,  statement_start_offset / 2, ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END)  - statement_start_offset) / 2)    +  --convert(varchar(8000),s2.text) +   '<br>'
   '<HR>'
FROM sys.dm_exec_requests DR  INNER JOIN sys.dm_exec_sessions DS  ON DR.Session_id = DS.Session_id
CROSS APPLY sys.dm_exec_sql_text(dr.sql_handle) AS s2 
WHERE DR.Session_ID in
 (SELECT blocking_session_id
   FROM sys.dm_exec_requests 
   WHERE (wait_time/15000) > @maxtime)   
         
   
 --BUILD WAITING PROCESSES EMAIL MSG   
 select @WAITemailmsg = ' The Following Processes are Waiting for Resources:(BEING BLOCKED) <br> '
 select @WAITemailmsg = @WAITemailmsg +
   '<b> SPID: </b>'+ convert(varchar(5),DR.Session_ID) +    '<br>' +
   '<b>  CMD: </b>' +DR.command+    '<br>'+
   '<b>  DB:</b> '+ db_name(DR.database_id) +   '<br>' + 
   '<b>  LOGIN:</b> '+ DS.login_name +   '<br>' + 
   ' <b> WAITTIME:</b> '+ convert(varchar(4),DR.wait_time/15000)+   '<br>' +
   ' <b> BLOCKED BY:</b> '+ convert(varchar(5),DR.blocking_session_id) +   '<br>'   + 
 '<b> wAIT rESOURCE: </b>'+wait_resource + '<br>' +
 '<b> last_wait_type: </b>'+last_wait_type + '<br>' +
 ' <b> SQL Statement: </b>' +  (select name from [YOURDB].sys.objects where object_id=s2.objectid)+' '+  SUBSTRING(s2.text,  statement_start_offset / 2, ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END)  - statement_start_offset) / 2)    + '<br>' --convert(varchar(8000),s2.text) +   '<br>'
FROM sys.dm_exec_requests DR 
INNER JOIN sys.dm_exec_sessions DS  ON DR.Session_id = DS.Session_id
CROSS APPLY sys.dm_exec_sql_text(dr.sql_handle) AS s2 
WHERE   (DR.wait_time/15000) > @maxtime
 and DR.blocking_session_id <> 0
SELECT @message =  @BLKemailmsg + ' ' + @WAITemailmsg
      exec msdb..sp_send_dbmail  @profile_name =  @MailProfile
     ,  @recipients =  @recipients
     ,  @subject =  'Blocking Processes'
     ,  @body_format =  'HTML'
     ,  @body = @message
  ,@query_result_header = 0
 END
--