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