-- Stored Procedure: dbo.GetBlockingDetails
-- Bill Wunder
use admin
GO
SET QUOTED_IDENTIFIER ON 
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) = '<probably the DBA group>'
 , @SQLrecipients varchar(100) = '<sql developers>'
 , @POC1recipients varchar(100) = '<alt point of contact>'  -- base on   
 , @POC2recipients varchar(100) = '<another alt point of contact>'
AS
/*******************************************************************************************************
*	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 'bill.wunder@wallst.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 functional area point of contact
********************************************************************************************************/

---------------------------------------------
-- 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
 , @blockDuration smallint -- control log timing and measure total block time
 , @InputBuffer varchar(255)
 , @SQLStmt varchar(255)
 
---------------------------------------------
-- create temp tables
---------------------------------------------

declare @blockers table 
 ( spid smallint
 , kpid smallint
 , loginName varchar(128)
 , inputBuffer varchar(255)
	, fn_get_sql varchar(255))

declare @blocked table 
	( spid smallint
 , kpid smallint
 , blocked smallint
 , inputBuffer varchar(255)
	, fn_get_sql varchar(255))

---------------------------------------------
-- 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 the head of each
 could be a processes running in paralled so make sure only one row (kpid)
 and try for one that has executing statement info (non-zero stmt_end)
*/

insert @blockers 
 ( spid
 , kpid
 , loginName)
select 
   blkr.spid
 , blkr.kpid 
 , blkr.loginame
from master.dbo.sysprocesses blkr with(nolock)
join master.dbo.sysprocesses blkd with(nolock)
on blkr.spid = blkd.blocked 
where blkr.blocked = 0

-- condense multi-threaded spids 
delete @blockers 
where kpid not in (select min(kpid) 
                   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 a little time to kill so set up notification list now
  set @Recipients = @AlwaysNotifyRecipients 
  if @@servername in '<Procution Server List>'
   begin

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

    if exists (select 1 from @blockers
               where loginName like '%<login used by functional area 1>%')
    and charindex(@POC1Recipients,@Recipients) = 0
     set @Recipients = @Recipients + ',' + @POC1Recipients

    if exists (select 1 from @blockers
               where loginName like '%<login used by functional area 2>%')
    and charindex(@POC2Recipients,@Recipients) = 0
     set @Recipients = @Recipients + ',' + @POC2Recipients

   end

  -- using a simple waitfor of @blockduration can cause unpredictable delay
  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
    	, 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(b.LoginName,1,20) as varchar(20))
    	, cast(substring(p.hostname,1,15) as varchar(15))
     , b.inputbuffer
     , b.fn_get_sql
    from master.dbo.sysprocesses p with(nolock)
    join @blockers b
    on p.spid = b.spid
    and p.kpid = b.kpid
    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) 

    set @Recipients = @AlwaysNotifyRecipients 

    insert @blocked 
     ( spid
     , kpid
     , blocked)
    select 
       spid
     , kpid
     , blocked 
    from master.dbo.sysprocesses with(nolock)
    where blocked > 0

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


    -- just get sql info on first level blocked spids
 			select @spid = min(spid) 
    from @blocked blkd
    
    -- each time through 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 p.kpid = b.kpid
                 and b.blocked = 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 

   			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
    	, 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))
     , b.inputbuffer
     , b.fn_get_sql  
    from master.dbo.sysprocesses p with(nolock)
    join @blocked b
    on p.spid = b.spid
    and p.kpid = b.kpid
    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) 

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

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

    select @Subject = @@servername + ' Block condition has existed for 30 seconds. ' 
                    + cast((@rows) as varchar(10))
                    + ' process(es) are now blocked' 
				
    select @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 ans 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 > 0


    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.'	+ 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 and b.kpid = blkr.kpid
                         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 about ' 
                  + 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 ON 
GO
SET ANSI_NULLS ON 
GO