alert on blocking chains via email

, 2004-09-01 (first published: )

This is a script I use to watch for blocking chains on a server there are four variables to be set. @Duration tells it how long to run. This will be an active thread for the duration. @IntervalSec how often to poll for blocking. @maxwaittime time in miliseconds a thread that is blocked that you wish to allert on 30000 is 30 seconds. If a thread has been blocked that long alerts will start going out. @recivers, people who get the emails.

You can set it up as a job to start it will always show as executing until the duration runs out. I have it set to run every minute just in case there is a thread exit it will restart the job again.

---------------------------------------------------------------------------------------------------
--pull input buffer from blocked threads 
--This query pulls spids and then processes those spids through the dbcc inputbuffer to get
--what they are doing. This is to aid in troubleshooting problems by getting as much detail
--as possible on running spids
---------------------------------------------------------------------------------------------------
/*
--this is the table to write the data to
CREATE TABLE [dbo].[blocking] (
	[tstamp] [datetime] NOT NULL ,
	[spid] [int] NULL ,
	[blocked] [int] NULL ,
	[waittype] [varchar] (255) NULL ,
	[waittime] [bigint] NULL ,
	[physical_io] [bigint] NULL ,
	[cpu_in_seconds] [bigint] NULL ,
	[memusage] [bigint] NULL ,
	[name] [nvarchar] (128)  NOT NULL ,
	[open_tran] [tinyint] NULL ,
	[status] [varchar] (20)  NULL ,
	[hostname] [varchar] (50)  NULL ,
	[program_name] [varchar] (100)  NULL ,
	[cmd] [varchar] (100)  NULL ,
	[nt_domain] [varchar] (100)  NULL ,
	[nt_username] [varchar] (200)  NULL ,
	[loginame] [varchar] (100)  NULL ,
	[EventType] [varchar] (255)  NULL ,
	[Parameters] [varchar] (255)  NULL ,
	[EventInfo] [varchar] (255)  NULL ,
	[text] [text]  NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

*/
set nocount on

use <database you want data written to>

CREATE TABLE #tbl_fn_get_sql (
	[dbid] [smallint] NULL ,
	[objectid] [int] NULL ,
	[number] [smallint] NULL ,
	[encrypted] [bit] NOT NULL ,
	[text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) 

create table #active_spids
(
	spid int,
	blocked int,
	waittype varchar(255),
	waittime bigint,
	physical_io bigint,
	cpu bigint,
	memusage bigint,
	dbid int,
	open_tran tinyint,
	status varchar(20),
	hostname varchar(50),
	program_name varchar(100),
	cmd varchar(100),
	nt_domain varchar(100),
	nt_username varchar(200),
	loginame varchar(100),
	[sql_handle] [binary] (20) NOT NULL ,
	[stmt_start] [int] NOT NULL ,
	[stmt_end] [int] NOT NULL 

)

create table #active_spids_info
(
	spid int,
	blocked int,
	waittype varchar(255),
	waittime bigint,
	physical_io bigint,
	cpu bigint,
	memusage bigint,
	dbid int,
	open_tran tinyint,
	status varchar(20),
	hostname varchar(50),
	program_name varchar(100),
	cmd varchar(100),
	nt_domain varchar(100),
	nt_username varchar(200),
	loginame varchar(100),
	[sql_handle] [binary] (20) NOT NULL ,
	[stmt_start] [int] NOT NULL ,
	[stmt_end] [int] NOT NULL,
	EventType varchar(255),
	Parameters varchar(255),
	EventInfo varchar(255),
	[text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

create table #event_info
(
	spid int,
	EventType varchar(255),
	Parameters varchar(255),
	EventInfo varchar(255)
)


DECLARE 
	@TerminateGatheringDT  datetime -- when to stop gathering
	, @WaitFor_Interval datetime
	, @LastRecordingDT datetime
	, @RecordingDT datetime
	, @myError int            -- Local copy of @@ERROR
	, @myRowCount int         -- Local copy of @@RowCount
	, @msgText nvarchar(4000) -- for error messages
	, @dbname varchar(255)
	, @svrname varchar(255)	
	, @datestart as datetime
	, @Duration datetime -- Duration of data collection
	, @IntervalSec int -- Approx sec in the gathering interval
	, @tstamp varchar(255)
	, @spid1 varchar(255)
	, @dbname1 varchar(255)
	, @status varchar(255)
	, @hostname varchar(255)
	, @programname varchar(255)
	, @cmd varchar(255)
	, @nt_domain varchar(255)
	, @nt_username varchar(255)
	, @loginame varchar(255)
	, @text varchar(8000)
	, @msg varchar(8000)
	, @sub varchar(8000)
	, @timestamp as datetime
	, @spid int
	, @sqlhandle binary(20)
	, @tsqlhandle as varchar(255)
	, @waittime varchar(255)
	, @waittype varchar(255)
	, @buffer varchar(255)
	, @maxwaittime int
	, @recivers varchar(8000)
	, @diffmsec bigint
set nocount on

	set @Duration = '08:00:00' -- Duration of data collection
	set @IntervalSec = 30 -- Approx sec in the gathering interval
	set @maxwaittime = 28000 -- This is in miliseconds!!!
	set @recivers = '<email addresses here>' --who all gets the emails
	SET @diffmsec = DATEDIFF(ms
                             , CONVERT(datetime, '00:00:00', 8)
                             , @Duration)

SELECT @WaitFor_Interval = DATEADD (s, @IntervalSec , 
	CONVERT (datetime, '00:00:00', 108)
                                 )
     , @TerminateGatheringDT = DATEADD(ms, @diffmsec,getdate())

WHILE getdate() <= @TerminateGatheringDT BEGIN

truncate table #active_spids
truncate table #active_spids_info
truncate table #event_info
truncate table #tbl_fn_get_sql

insert into #active_spids
select 
	spid,
	blocked,
	waittype,
	waittime,
	physical_io,
	cpu,
	[memusage],
	a.dbid,
	open_tran,
	a.status,
	hostname,
	program_name,
	cmd,
	nt_domain,
	nt_username,
	loginame,
	[sql_handle],
	[stmt_start],
	[stmt_end]
from
	(
		select 
			spid,
			blocked,
			'waittype' = 
			CASE
				WHEN waittype = 0x0001 THEN 	'Exclusive table lock'
				WHEN waittype = 0x0003 THEN 	'Exclusive intent lock'
				WHEN waittype = 0x0004 THEN 	'Shared table lock'
				WHEN waittype = 0x0005 THEN 	'Exclusive page lock'
				WHEN waittype = 0x0006 THEN 	'Shared page lock'
				WHEN waittype = 0x0007 THEN 	'Update page lock'
				WHEN waittype = 0x0013 THEN 	'Buffer resource lock (exclusive) request'
				WHEN waittype = 0x0013 THEN 	'Miscellaneous I/O (sort, audit, direct xact log I/O)'
				WHEN waittype = 0x0020 THEN 	'Buffer in I/O'
				WHEN waittype = 0x0022 THEN 	'Buffer being dirtied'
				WHEN waittype = 0x0023 THEN 	'Buffer being dumped'
				WHEN waittype = 0x0081 THEN 	'Write the TLog'
				WHEN waittype = 0x0200 THEN 	'Parallel query coordination'
				WHEN waittype = 0x0208 THEN 	'Parallel query coordination'
				WHEN waittype = 0x0420 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0421 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0422 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0423 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0424 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0425 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0800 THEN 	'Network I/O completion'
				WHEN waittype = 0x8001 THEN 	'Exclusive table lock'
				WHEN waittype = 0x8003 THEN 	'Exclusive intent lock'
				WHEN waittype = 0x8004 THEN 	'Shared table lock'
				WHEN waittype = 0x8005 THEN 	'Exclusive page lock'
				WHEN waittype = 0x8006 THEN 	'Shared page lock'
				WHEN waittype = 0x8007 THEN 	'Update page lock'
				WHEN waittype = 0x8011 THEN 	'Buffer resource lock (shared) request'
			ELSE	'OLEDB/Miscellaneous'
			END,
			waittime,
			physical_io,
			cpu,
			[memusage],
			dbid,
			open_tran,
			status,
			hostname,
			program_name,
			cmd,
			nt_domain,
			nt_username,
			loginame,
			[sql_handle],
			[stmt_start],
			[stmt_end]
 		from 
			master.dbo.sysprocesses with(NOLOCK)
		where 
			blocked > 0 and waittime > @maxwaittime
		union all
		select 
			spid,
			blocked,
			'waittype' = 
			CASE
				WHEN waittype = 0x0001 THEN 	'Exclusive table lock'
				WHEN waittype = 0x0003 THEN 	'Exclusive intent lock'
				WHEN waittype = 0x0004 THEN 	'Shared table lock'
				WHEN waittype = 0x0005 THEN 	'Exclusive page lock'
				WHEN waittype = 0x0006 THEN 	'Shared page lock'
				WHEN waittype = 0x0007 THEN 	'Update page lock'
				WHEN waittype = 0x0013 THEN 	'Buffer resource lock (exclusive) request'
				WHEN waittype = 0x0013 THEN 	'Miscellaneous I/O (sort, audit, direct xact log I/O)'
				WHEN waittype = 0x0020 THEN 	'Buffer in I/O'
				WHEN waittype = 0x0022 THEN 	'Buffer being dirtied'
				WHEN waittype = 0x0023 THEN 	'Buffer being dumped'
				WHEN waittype = 0x0081 THEN 	'Write the TLog'
				WHEN waittype = 0x0200 THEN 	'Parallel query coordination'
				WHEN waittype = 0x0208 THEN 	'Parallel query coordination'
				WHEN waittype = 0x0420 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0421 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0422 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0423 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0424 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0425 THEN 	'Buffer I/O latch'
				WHEN waittype = 0x0800 THEN 	'Network I/O completion'
				WHEN waittype = 0x8001 THEN 	'Exclusive table lock'
				WHEN waittype = 0x8003 THEN 	'Exclusive intent lock'
				WHEN waittype = 0x8004 THEN 	'Shared table lock'
				WHEN waittype = 0x8005 THEN 	'Exclusive page lock'
				WHEN waittype = 0x8006 THEN 	'Shared page lock'
				WHEN waittype = 0x8007 THEN 	'Update page lock'
				WHEN waittype = 0x8011 THEN 	'Buffer resource lock (shared) request'
			ELSE	'OLEDB/Miscellaneous'
			END,
			waittime,
			physical_io,
			cpu,
			[memusage],
			dbid,
			open_tran,
			status,
			hostname,
			program_name,
			cmd,
			nt_domain,
			nt_username,
			loginame,
			[sql_handle],
			[stmt_start],
			[stmt_end]
		from 
			master.dbo.sysprocesses with(NOLOCK)
		where
			spid in
			( 
				select 
					blocked 
				from 
					master.dbo.sysprocesses with(NOLOCK)
				where 
					blocked > 0 and waittime > @maxwaittime
			) 
	) a
order by blocked

--loop through the spids without a cursor
while (select count(spid) from #active_spids) > 0
begin
	set @spid = (select top 1 spid from #active_spids order by spid)
	--grab the top spid
	insert into #active_spids_info 
	(
		spid,
		blocked,
		waittype,
		waittime,
		physical_io,
		cpu,
		[memusage],
		dbid,
		open_tran,
		status,
		hostname,
		program_name,
		cmd,
		nt_domain,
		nt_username,
		loginame,
		[sql_handle],
		[stmt_start],
		[stmt_end] 
	)
	select top 1
		spid,
		blocked,
		waittype,
		waittime,
		physical_io,
		cpu,
		[memusage],
		dbid,
		open_tran,
		status,
		hostname,
		program_name,
		cmd,
		nt_domain,
		nt_username,
		loginame,
		[sql_handle],
		[stmt_start],
		[stmt_end] 
	from 
		#active_spids 
	order by 
		spid

	insert into #event_info (EventType,Parameters,EventInfo) EXEC('DBCC INPUTBUFFER (' + @spid + ') WITH NO_INFOMSGS')
	--get the inputbuffer 

	exec('update #event_info set spid = '+@spid+' where spid IS NULL')
	--add the spid to the input buffer data

	select @sqlhandle = sql_handle from #active_spids where spid = @spid

	insert into #tbl_fn_get_sql 
	select * from ::fn_get_sql(@sqlhandle)

	UPDATE #active_spids_info 
		SET 
		#active_spids_info.text = #tbl_fn_get_sql.text
		FROM
			#active_spids_info,#tbl_fn_get_sql
		WHERE 
			#active_spids_info.spid = @spid

	truncate table #tbl_fn_get_sql

	delete from #active_spids where spid = @spid
	--remove the spid processed
end

UPDATE #active_spids_info 
	SET 
	#active_spids_info.EventType = #event_info.EventType,
	#active_spids_info.Parameters = #event_info.Parameters,
	#active_spids_info.EventInfo = #event_info.EventInfo
	FROM
		#active_spids_info, #event_info
	WHERE 
		#active_spids_info.spid = #event_info.spid
--join all the info into one table

set @timestamp = getdate()
--select statement to return results 
insert into management.dbo.blocking
select	@timestamp as tstamp,
	a.spid,
	a.blocked,
	a.waittype,
	a.waittime,
	a.physical_io,
	(a.cpu/1000) as cpu_in_seconds,
	a.[memusage],
	b.[name],
	a.open_tran,
	a.status,
	a.hostname,
	a.program_name,
	a.cmd,
	a.nt_domain,
	a.nt_username,
	a.loginame,
	a.EventType,
	a.Parameters,
	a.EventInfo,
	a.text
from 
	#active_spids_info a
inner join
	master.dbo.sysdatabases b
on
	a.dbid = b.dbid
if ((select max(tstamp) from management.dbo.blocking where blocked = 0) = @timestamp)
begin
	select @sub = 'Blocking Issues - '+cast(serverproperty('servername') as varchar(255))
	
	select 
		@tstamp = tstamp,
		@spid1 = spid,
		@status = status,
		@hostname = isnull(hostname,''),
		@programname = isnull(program_name,''),
		@cmd = isnull(cmd,''),
		@nt_domain = isnull(nt_domain,''),
		@nt_username = isnull(nt_username,''),
		@loginame = isnull(loginame,''),
		@text = isnull([text],''),
		@waittime = (select max(waittime) from management.dbo.blocking where tstamp = (select max(tstamp) from management.dbo.blocking)),
		@waittype = isnull(waittype,''),
		@buffer = isnull(EventInfo,'')
	
	from  
		management.dbo.blocking 
	where 
		tstamp = (
				select max(tstamp) 
				from 
					management.dbo.blocking) and blocked = 0


	select @msg ='The user below is at the head of the blocking chain on the listed server:'+char(13)+
	'__________________________________________________________________________'+char(13)+
	'TimeStamp: '+@tstamp+char(13)+
	'SPID: '+@spid1+char(13)+
	'Login Name: '+@loginame+char(13)+
	'NT Domain: '+@nt_domain+char(13)+
	'NT Username: '+@nt_username+char(13)+
	'Host Name: '+@hostname+char(13)+
	'Command: '+@cmd+char(13)+
	'Program Name: '+@programname+char(13)+
	'Wait Type: '+@waittype+char(13)+
	'Maximum Wait Time For Blocked Thread: '+@waittime+char(13)+char(13)+
	'Input Buffer: '+@buffer+char(13)+
	'Status: '+@status+char(13)+
	'SQL String:'+char(13)+
	'--WARNING CAN BE LONG AND MAY NOT BE THE WHOLE TEXT!!!--'+char(13)+@text
	
	EXEC master.dbo.xp_sendmail @recipients = @recivers, 
	   @subject = @sub,
	   @message = @msg


/*
--	just used to debug and make sure email is running	
	EXEC master.dbo.xp_sendmail @recipients = 'wbrown@thescooterstore.com', 
	@subject = 'test',
	@message = 'test'
*/
end

WAITFOR DELAY @WaitFor_Interval   -- delay      
END

drop table #active_spids
drop table #active_spids_info
drop table #event_info
drop table #tbl_fn_get_sql
set nocount off

Rate

5 (1)

Share

Share

Rate

5 (1)

Related content

Run a Script Against All User Databases

Now you can run administrative T-SQL tasks against all your databases in one shot. Very usefull for environments that have a seperate database for each client, ASP's, etc. It's pretty straight forward and can be used for almost any task. This example Looks for a table and prints the database name and selects from the […]

2001-10-21

3,029 reads

Find Longstanding Open Transactions

Ever forget to commit a transaction and then find out hours later that there is deadlocked transactions all over your database server? Worse yet has one of your coworkers done this to your database server? Never again, this stored procedure will net send the machine that has an open transaction, and send you (The DBA) […]

4 (1)

2001-10-08

8,740 reads