use admin
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBlockingDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetBlockingDetails]
GO






CREATE PROCEDURE [dbo].[GetBlockingDetails] 
  	@AlwaysNotifyRecipients varchar(112) = 'DBAs@your.com'
 , @SQLRecipients varchar(100) = 'DBDevelopers@your.com'
 , @DMARecipients varchar(100) = 'ETLgroup@your.com'
 , @DMRecipients varchar(100) = 'DM@your.com'
 , @NetIQRecipients varchar(100) = 'NOS@your.com'
 S
/*******************************************************************************************************
*	admin.dbo.GetBlockingDetails 
*	Creator:       bw
*	Date:          9-29-2003
*
*	Description:   run time blocking chain tracker with notification and logging
*	Notes:         run once a minute from a scheduled job. Might need to monitor for changing last_batch 
*                of blocker if open transaction on a persistent connection is the problem 
*
*	Usage:
	     EXECUTE admin.dbo.GetBlockingDetails 'you@your.com'
*
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*           bw                10-20-03 add sp_lock info for the blocker (max 200 rows)
*           bw                06-27-04 check BlockingDetails at insert to avoid dup key error
*           bw                07-13-04 notify dag poc when blocker is a workoff
*           bw                07-28-04 join to @blocked when building @blockers 
*                                       - still trying to eliminate dup key errors...
*                                      try to not notify SQL if NETIQ is blocking 
*           bw                01-31-05 make @BlockeDuration an int to avoid a rare overflow
*           bw                03-28-05 notify if acceptance, add xref notify   
*           bw                03-30-05 track complete blocking chain       
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @spid smallint
 , @Recipients varchar(512)
	, @Subject varchar(100)
	, @Message varchar(300)
	, @Query varchar(1000) 
	, @blocker smallint
	, @Dt datetime
	, @recCreatedDt varchar(20)
 , @rows int
 , @error int
 , @blockDuration int -- control log timing and measure total block time
 , @InputBuffer varchar(255)
 , @SQLStmt varchar(255)
 
---------------------------------------------
-- create temp tables
---------------------------------------------

declare @blockers table 
 ( spid smallint
	, blockedBy smallint not null
	, waitResource varchar(50)
	, lastBatch varchar(20)
	, programName varchar(36)
	, loginName varchar(20)
	, hostName varchar(15)
 , inputBuffer varchar(255)
	, fn_get_sql varchar(255)
 , id int identity(1,1))

declare @blocked table 
 ( spid smallint
	, blockedBy smallint not null
	, waitResource varchar(50)
	, lastBatch varchar(20)
	, programName varchar(36)
	, loginName varchar(20)
	, hostName varchar(15)
 , inputBuffer varchar(255)
	, fn_get_sql varchar(255)
 , id int identity(1,1))

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON

set @Dt = getdate()
set @recCreatedDt = @Dt
set @blockDuration = 30 -- min time that block must persist to be logged
---------------------------------------------
-- body of stored procedure
---------------------------------------------

-- Don't use a temp table so it can be queried later
if object_id('admin.dbo.BlockingDetails','U') is null
	create table admin.dbo.BlockingDetails 
  ( recCreatedDt varchar(20) not null
		, spid smallint not null
		, blockedBy smallint not null
		, waitResource varchar(50)
		, lastBatch varchar(20)
		, programName varchar(36)
		, loginName varchar(20)
		, hostName varchar(15)
		, inputBuffer varchar(255)
		, additionalInfo varchar(255)
		, blockEndTime varchar(20)
		, fn_get_sql varchar(7000)
		, constraint pk_BlockingDetails__recCreatedDt__BlockedBy__spid 
				primary key (recCreatedDt, blockedBy, spid))

if columnproperty(object_id('admin.dbo.BlockingDetails'),'spid','Precision') = 10
 begin
  alter table admin.dbo.BlockingDetails
  drop constraint pk_BlockingDetails__recCreatedDt__BlockedBy__spid 

  alter table admin.dbo.BlockingDetails alter column recCreatedDt varchar(20) not null
  alter table admin.dbo.BlockingDetails alter column BlockedBy smallint not null
  alter table admin.dbo.BlockingDetails alter column spid smallint not null

  exec sp_executesql N'alter table admin.dbo.BlockingDetails
    add constraint pk_BlockingDetails__recCreatedDt__BlockedBy__spid 
		  primary key (recCreatedDt, blockedBy, spid)'

  return
 end 

if not exists (select 1 from master.dbo.sysprocesses with(nolock) where blocked > 0)
 goto NothingToDo

/*
 could be more than one blocking chain, get all blockers in each chain
 could be a processes running in paralled so make sure only one row (by tablevar id)
 and try for one that has executing statement info (non-zero stmt_end)
*/

insert @blockers 
 ( spid
	, blockedBy
	, waitResource
	, lastBatch
	, programName
	, loginName
	, hostName)
select 
	  p.spid
	, p.blocked
	, substring(p.waitresource,1,50)
	, cast(p.last_batch as varchar(20))
	, case when p.program_name like 'SQLAgent - TSQL JobStep%'
		  then 'Job: ' + substring(j.name,1,30)
		  else substring(p.program_name,1,35)
		  end
	, cast(substring(p.loginame,1,20) as varchar(20))
	, cast(substring(p.hostname,1,15) as varchar(15))
from master.dbo.sysprocesses p with(nolock)
left join msdb.dbo.sysjobs j with(nolock)
on substring(isnull(p.program_name,''),charindex('0x', isnull(p.program_name,'')) + 18, 16) 
	= substring(replace(isnull(j.job_id,''), '-',''),17,16) 
where p.spid in (select blocked 
                 from master.dbo.sysprocesses with(nolock)
                 where blocked > 0) 
--and p.blocked = 0

-- remove dups
delete @blockers
where id not in (select min(id) 
                 from @blockers 
                 group by spid)

-- get the current query info for blocking process
select @blocker = min(spid) from @blockers

while @blocker is not null
	begin

  select @InputBuffer = null, @SQLStmt = null
  exec admin.dbo.GetSQL @blocker, 0, @InputBuffer output, @SQLStmt output

		update @blockers
		set inputbuffer = @InputBuffer
			, fn_get_sql = @SQLStmt
		where spid = @blocker

  select @blocker = min(spid) from @blockers where spid > @blocker

	end -- one blocker at a tiime

-- allow any cleared blockers to fall out of the watched group
delete blkr
from @blockers blkr
left join master.dbo.sysprocesses blkd with(nolock)
on blkr.spid = blkd.blocked
where blkd.blocked is null

-- keep going if any of these spids are still blocking
if exists (select 1 from @blockers)
	begin

  -- have some time to kill so set filter notification recipients 
  -- for non production systems now
/* Don't include this but leave it in in case someone want's to use the concept

  set @Recipients = @AlwaysNotifyRecipients 
 -- apply rules for non development boxes 
 if NOT(@@servername like 'DEV%') 
   begin

    -- add database developers recipients to identified blocks 
    if exists (select 1 from @blockers
                where loginName <> 'netiq'
                and inputbuffer not like '%dbo.PurgeAllData%'
                and inputbuffer not like '%dbo.DeleteOldArchiveData%'
                and inputbuffer not like '%dbo.task_weekly%')
     set @Recipients = @Recipients + isnull(',' + @SQLRecipients,'')

    -- add systems folks to netiq blocks
    if exists (select 1 from @blockers
                where loginName = 'netiq'
                or inputbuffer like '%dbo.PurgeAllData%'
                or inputbuffer like '%dbo.DeleteOldArchiveData%'
                or inputbuffer like '%dbo.task_weekly%')
     set @Recipients = @Recipients + isnull(',' + @NetIQrecipients,'')

    -- add ETL folks to ETL related blocks
    if exists (select 1 from @blockers
               where loginName like '%SVCDMA3%')
    and charindex(@DMA3Recipients,@Recipients) = 0
     set @Recipients = @Recipients + isnull(',' + @DMA3Recipients,'')

    -- add folks for blocks to be included only for certain boxes    
    -- note that service name is similar to above so need adequate filter 
   if exists (select 1 from @blockers
               where loginName like '%svcDM%' 
               and loginName not like '%SVCDMA%')
    or @@servername like 'DMP%'
    or @@servername like 'DEWEYP%'

    if charindex(@DMRecipients,@Recipients) = 0
      set @Recipients = @Recipients + isnull(',' + @DMRecipients,'')

    if exists (select 1 from @blockers
               where loginName like '%NS%')
    or @@servername in ('NSSQL1','HOMER')
    set @Recipients = @Recipients + isnull(',' + @DERecipients,'')

   end

*/


  -- using a simple wait for here can cause the longer than expected delay depending on how long above runs
  -- wait for the difference between the proc start and current time to exceed @blockDuration threshold
  while DateDiff(ss, @Dt, getdate()) < @blockDuration
  		waitfor delay '000:00:01' 

  -- allow any cleared blockers to fall out of the watched group
  delete blkr
  from @blockers blkr
  left join master.dbo.sysprocesses blkd with(nolock)
  on blkr.spid = blkd.blocked
  where blkd.blocked is null

		if exists (select 1 from @blockers)
			begin					

    -- blocks now qualify as long running so log the head of the chain and see who is being blocked
    insert admin.dbo.BlockingDetails 
     ( recCreatedDt
    	, spid
    	, blockedBy
    	, waitResource
    	, lastBatch
    	, programName
    	, loginName
    	, hostName
     , inputbuffer
     , fn_get_sql)
    select 
       @recCreatedDt
    	, spid
    	, blockedBy
    	, waitResource
    	, lastBatch
    	, programName
    	, loginName
    	, hostName
     , b.inputbuffer
     , b.fn_get_sql
    from @blockers b
    where exists (select 1 
                  from master.dbo.sysprocesses with(nolock) 
                  where spid = b.spid 
                  and blocked = b.blockedBy)
    
    -- bail if no blocking spids inserted
    if @@rowcount = 0 
     goto NothingToDo

    -- start tracking the blocked processes
    insert @blocked 
     ( spid
    	, blockedBy
    	, waitResource
    	, lastBatch
    	, programName
    	, loginName
    	, hostName)
    select 
    	  p.spid
    	, p.blocked
    	, substring(p.waitresource,1,50)
    	, cast(p.last_batch as varchar(20))
    	, case when p.program_name like 'SQLAgent - TSQL JobStep%'
		      then 'Job: ' + substring(j.name,1,30)
    		  else substring(p.program_name,1,35)
		      end
    	, cast(substring(p.loginame,1,20) as varchar(20))
    	, cast(substring(p.hostname,1,15) as varchar(15))
    from master.dbo.sysprocesses p with(nolock)
    left join msdb.dbo.sysjobs j with(nolock)
    on substring(isnull(p.program_name,''),charindex('0x', isnull(p.program_name,'')) + 18, 16) 
    	= substring(replace(isnull(j.job_id,''), '-',''),17,16) 
    where p.blocked in (select spid from @blockers)
    and p.spid not in (select spid from @blockers)

    delete @blocked 
    where id not in (select min(id) 
                     from @blocked 
                     group by spid)

    -- get sql info
 			select @spid = min(spid) 
    from @blocked blkd
    
    -- make sure the blocking condition still exists
				while @spid is not null
     begin

      if exists (select 1 from master.dbo.sysprocesses p with(nolock)
                 join @blocked b
                 on p.spid = b.spid
                 and b.blockedBy = p.blocked   
  			  							   where b.spid = @spid)

       begin
        exec admin.dbo.GetSQL @spid, 0, @InputBuffer output, @SQLStmt output
    
      		update @blocked
    	  	set inputbuffer = @InputBuffer
    			  , fn_get_sql = @SQLStmt
      		where spid = @spid
       end 
      else 
       delete @blocked where spid = @spid

   			select @spid = min(spid) 
      from @blocked blkd
      where spid > @spid
      
      if @@rowcount = 0 set @spid = null

     end 

    insert admin.dbo.BlockingDetails 
     ( recCreatedDt
    	, spid
    	, blockedBy
    	, waitResource
    	, lastBatch
    	, programName
    	, loginName
    	, hostName
     , inputbuffer
     , fn_get_sql)
    select 
       @recCreatedDt
    	, spid
    	, blockedBy
    	, waitResource
    	, lastBatch
    	, programName
    	, loginName
    	, hostName
     , b.inputbuffer
     , b.fn_get_sql
    from @blocked b
    where exists (select 1 from master.dbo.sysprocesses with(nolock)
                  where spid = b.spid 
                  and blocked = b.blockedBy)

    -- bail if no blocked spids inserted
    if @@rowcount = 0 
     goto NothingToDo

    select @rows = count(distinct spid) from master.dbo.sysprocesses with(nolock) where blocked in (select spid from @blockers)

				select @Subject = @@servername + ' Block condition has existed for 30 seconds. ' 
						+ cast((@rows) as varchar(10))
						+ ' process(es) are now blocked in this chain' 
				
				set @Message = 'For additional details:' + char(13) + char(10) + char(9)
												+ 'Select * from admin.dbo.BlockingDetails where recCreatedDt = ''' + @recCreatedDt + ''''	

				select @Query =  
'set nocount on 
select spid, blockedBy,	programName, loginName, hostName 
 , char(13) + char(10) + cast(substring(Inputbuffer,1,100) as varchar(100)) 
  +  char(13) + char(10) [Input Buffer]
from admin.dbo.BlockingDetails with(nolock)
where recCreatedDt = ''' + @recCreatedDt + ''' 
order by BlockedBy 
exec admin.dbo.GetLocks null,''True'',''False'''  

				exec sysmon.dbo.safe_sendmail @recipients = @Recipients 
								, @subject = @Subject
								, @message = @Message
								, @query = @Query
								, @width = 133
				
				-- add the inputbuffer and current sql of each blocked spid to the details table
				select @spid = min(spid) 
				from @blocked
				while @spid is not null				
					begin
      -- make sure still a blocked process in the current blocking incident
						if not exists (select 1 from master.dbo.sysprocesses with(nolock)
    									    				where spid = @spid 
				    									    and blocked <> 0)
							update admin.dbo.BlockingDetails with(rowlock)
							set additionalInfo = 'No longer blocked, may have timed out', 
							    blockEndTime = cast(getdate() as varchar(20))
							where spid = @spid
							and recCreatedDt = @recCreatedDt			
		
      -- allow any cleared blockers to fall out of the watched group
      delete blkr
      from @blockers blkr
      left join master.dbo.sysprocesses blkd with(nolock)
      on blkr.spid = blkd.blocked
      where blkd.blocked is null

  				select @spid = min(spid) 
				  from @blocked
				  where spid > @spid	
     end -- spid not null

			end	-- blocks exist after initial wait interval		
  else
   goto NothingToDo

	end -- blocks exist before delay
else
 goto NothingToDo

-- keep going if any of these spids are still blocking
if exists (select 1 from @blockers)
	begin	
  set @blockDuration = 60
  while DateDiff(ss, @Dt, getdate()) < @blockDuration
 		waitfor delay '000:00:01' 

  -- allow any cleared blockers to fall out of the watched group
  delete blkr
  from @blockers blkr
  left join master.dbo.sysprocesses blkd with(nolock)
  on blkr.spid = blkd.blocked
  where blkd.blocked is null

		if exists (select 1 from @blockers)
			begin

    select @rows = count(distinct spid) from master.dbo.sysprocesses with(nolock) where blocked in (select spid from @blockers)

				set @Subject = @@servername + ' Blocked condition has existed for one minute!' 
				set @Message = 'Blocking notification will be suspended until currently identified blocking is resolved. ' 
     + 'There are currently a total of ' + cast(@rows as varchar(10)) + ' blocked processes in the identified chain(s).'	+ char(13) + char(10)
					+ 'For additional details:' + char(13) + char(10) + char(9)
					+ 'Select * from admin.dbo.BlockingDetails where recCreatedDt = ''' + @recCreatedDt + ''''

  		exec sysmon.dbo.safe_sendmail @recipients = @Recipients 
					, @message = @Message
					, @subject = @Subject

				while exists (select 1 from @blockers)
					begin	
      -- after another minute has gone by
      set @blockDuration = @blockDuration + 60
      while DateDiff(ss, @Dt, getdate()) < @blockDuration
      	waitfor delay '000:00:01' 


						-- check for still waiting blocked spids
						select @spid = min(spid) 
						from @blocked

						while @spid is not null				
							begin
 							-- if blocker active but blocked no longer active mark blocked as possible timout
								if not exists (select 1 from master.dbo.sysprocesses with(nolock) 
	     																	where spid = @spid 
						     												and blocked > 0)
									update admin.dbo.BlockingDetails with(rowlock)
									set additionalInfo = 'No longer blocked, may have timed out'
          , blockEndTime = getdate()
									where BlockingDetails.spid = @spid
									and recCreatedDt = @recCreatedDt
		
  						select @spid = min(spid) 
	 						from @blocked
								where spid > @spid
							end	-- spid not null		

						-- check that all blockers are still blocking		
						select @blocker = min(spid) from @blockers
						while @blocker is not null	
							begin
								if not exists (select 1 
                       from @blockers b
                       join master.dbo.sysprocesses blkr with(nolock) 
                       on b.spid = blkr.spid 
                       join master.dbo.sysprocesses blkd with(nolock) 
                       on b.spid = blkd.blocked 
														       		where b.spid = @blocker)
         begin
          -- if no longer blocking, remove from current consideration
										update admin.dbo.BlockingDetails with(rowlock)
										set additionalInfo = additionalInfo + ' No longer blocking'
           , blockEndTime = getdate()
	 								where BlockingDetails.spid = @spid
		 							and recCreatedDt = @recCreatedDt
           
          -- allow any cleared blockers to fall out of the watched group
          delete @blockers 
          where spid = @blocker
         end -- still blocking

								select @blocker = min(spid) from @blockers where spid > @blocker									
							end	-- blocker not null
					end	-- while still blockers					
			end  -- if still blockers
	end -- blocking for 30 seconds

set @Subject = @@servername + ' blocking detected at ''' + @recCreatedDt + ''' is now Resolved' 
set @Message = 'Total blocking incident duration was ' + cast(@blockDuration as varchar(10)) + ' seconds' + char(13) + char(10) 
               + 'Block monitoring will resume in one minute.'
exec sysmon.dbo.safe_sendmail @recipients = @Recipients 
 , @message = @Message
	, @subject = @Subject						

NothingToDo:

update admin.dbo.BlockingDetails with(rowlock)
set blockEndTime = getdate()
 , additionalInfo = 'block cleared in less than ' + cast(DateDiff(ss, @Dt, getdate()) as varchar(10)) + ' seconds'
where recCreatedDt = @recCreatedDt			
and blockEndTime is NULL

-- maintain the history table once a day
if (datepart(hh, getdate()) = 0) and (datepart(mi, getdate()) = 0)
	delete admin.dbo.BlockingDetails with(paglock)
	where cast(recCreatedDt as datetime) < getdate() - 180

RETURN






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO