Blocked Process script for deadlock correction

,

This script was created to find an application problem that was causing SQL Server blocked processes. The BlockedProcess table is created to log both the blocker and blocked processes. The sp_BlockedProcess stored procedure is created to handle the situation by: inserting all processes affected by the blocking, notifying the blocking application user, emailing the admins with the logged information, and killing the blocking process. A scheduled Job should be created to EXEC the stored procedure to begin the monitoring process.

In my use I found that the Job should be scheduled to run every 2 minutes and only log processes that have been blocked for 120 seconds, that way the blocking can only exist for 4 minutes. This seemed the best trade off for business as usual and catching the information needed to debug the application.

Andy

-- Create Blocked Process script
--
-- Search for %%% for areas that need customization
--
-- %%% Change Log database name
USE pubs
GO

PRINT 'Create BlockedProcess table'
if exists (select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 
		AND id = object_id(N'dbo.BlockedProcess') )
	drop table dbo.BlockedProcess
GO
-- Create the log table
CREATE TABLE dbo.BlockedProcess (
	BlockedProcessNo_PK int IDENTITY (1,1) NOT NULL ,
	spid smallint NULL ,
	blocked smallint NULL ,
	open_tran smallint NULL ,
	login_time datetime NULL ,
	last_batch datetime NULL ,
	loginname varchar (20) NULL ,
	hostname varchar (70) NULL ,
	secs money NULL ,
	db varchar (20) ,
	input_buffer varchar(1000)
	CreateDate datetime NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE dbo.BlockedProcess WITH NOCHECK ADD 
	CONSTRAINT BlockedProcess_CreateDate_DF DEFAULT (getdate()) FOR CreateDate,
	CONSTRAINT BlockedProcess_PK PRIMARY KEY CLUSTERED 
	(BlockedProcessNo_PK) ON [PRIMARY] 
GO

-- Create CheckForBlocking stored procedure
PRINT 'sp_CheckForBlocking'
IF EXISTS (SELECT * FROM sysobjects WHERE sysstat & 0xf = 4
		AND id = object_id('dbo.sp_CheckForBlocking') )
	DROP PROCEDURE dbo.sp_CheckForBlocking
GO
CREATE PROCEDURE sp_CheckForBlocking
AS
DECLARE @spid smallint
	, @hostname varchar(70)
	, @ExecStr varchar(8000)
	, @rc int
	, @msg varchar(8000)
	, @Active_DB varchar(20)
	, @InputBuffer varchar(1000)

-- %%% Change Monitored database name
SET @Active_DB = 'Northwind'

SET NOCOUNT ON
IF EXISTS(SELECT blocked FROM master.dbo.sysprocesses 
	WHERE dbid = DB_ID(@Active_DB) AND blocked > 0
		-- %%% In EM, Management, Current Activity..., Process Info
		-- Use the desired Application value(s)
	 	AND program_name IN('Visual Basic ', 'MyApplication') )
BEGIN -- block exists
	-- get the blocking spid
	SELECT @spid = spid, @hostname = hostname FROM master.dbo.sysprocesses
		-- %%% Adjust > 120 to the number of seconds a processes has been blocked
		-- before loging the data, notifying, and killing the Blocking process
		WHERE blocked = 0 AND (convert(money,getdate()-last_batch)* 86400.0) > 120
			AND spid IN (SELECT DISTINCT blocked FROM master.dbo.sysprocesses 
				WHERE dbid = DB_ID(@Active_DB) AND blocked > 0
					-- %%% In EM, Management, Current Activity..., Process Info
					-- Use the desired Application value(s)
				 	AND program_name IN('Visual Basic ', 'MyApplication') )
	IF @spid IS NOT NULL
		BEGIN
			CREATE TABLE #ProcInfo(
				EventType varchar(30),
				Parameters int,
				EventInfo varchar(255) )
			SET @ExecStr = 'DBCC INPUTBUFFER(' + CONVERT(varchar, @spid) + ') WITH NO_INFOMSGS'
			INSERT INTO #ProcInfo
			EXEC(@ExecStr)
			SELECT @InputBuffer = ISNULL('Type: '+EventType+', ','')
				+ISNULL('Param: '+CONVERT(varchar,Parameters)+', ','')
				+ISNULL('Buffer: '+EventInfo,'')
			FROM #ProcInfo
			DROP TABLE #ProcInfo
			-- record all processes affected by the blocking 
			-- blocker has blocked = 0 and last_batch that IS NOT NULL
			INSERT INTO BlockedProcess (spid, blocked, open_tran, login_time, 
				last_batch, loginname, hostname, secs, db, input_buffer)
			SELECT spid
				, blocked
				, open_tran
				, login_time
				, last_batch
				, convert(varchar(20),loginame)
				, convert(varchar(70),hostname)
				, convert(money,getdate()-last_batch)*86400.0
				, convert(varchar(20),DB_NAME(dbid))
				, CASE WHEN spid=@spid THEN @InputBuffer ELSE NULL END
			FROM master.dbo.sysprocesses
			WHERE (dbid = DB_ID(@Active_DB) AND blocked > 0
				-- %%% In EM, Management, Current Activity..., Process Info
				-- Use the desired Application value(s)
			 	AND program_name IN('Visual Basic ', 'MyApplication') ) 
				OR spid = @spid
			-- send message to user	
			-- %%% Change Application name [optional]
			SET @msg = 'Shut down application and restart' 
			SET @ExecStr='net send '+@hostname+' '+@msg
			-- %%% Comment if you do not wish to net send to the Blocking user
  			EXEC @rc=master.dbo.xp_cmdshell @ExecStr, no_output
			-- send email with all processes
			SET @msg = ''
			SELECT @msg = @msg+'SPID: '+CONVERT(varchar,spid)+CHAR(13)+CHAR(10)+ 
				'Blocked: '+CONVERT(varchar,blocked)+CHAR(13)+CHAR(10)+ 
				'Open Trans: '+CONVERT(varchar,open_tran)+CHAR(13)+CHAR(10)+ 
				'Login: '+CONVERT(varchar,login_time,121)+CHAR(13)+CHAR(10)+ 
				'Last Batch: '+CONVERT(varchar,last_batch,121)+CHAR(13)+CHAR(10)+ 
				'User: '+CONVERT(varchar,loginame)+CHAR(13)+CHAR(10)+ 
				'Machine: '+CONVERT(varchar,hostname)+CHAR(13)+CHAR(10)+ 
				'Seconds: '+CONVERT(varchar,CONVERT(money,getdate()-last_batch)*86400.0)+CHAR(13)+CHAR(10)+
				CASE WHEN spid=@spid THEN ISNULL('Blocker CMD: '+@InputBuffer,'') ELSE '' END +
				CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10) 
			FROM master.dbo.sysprocesses
			WHERE (dbid = DB_ID(@Active_DB) AND blocked > 0
				-- %%% In EM, Management, Current Activity..., Process Info
				-- Use the desired Application value(s)
			 	AND program_name IN('Visual Basic ', 'MyApplication') )
				OR spid = @spid
			-- %%% Change email address(es) for notification
			-- %%% Comment if you do not wish to send email
			EXEC master.dbo.xp_sendmail @recipients = 'notify@domain.com; admin@domain.com', 
				@message = @msg,
				-- %%% Change Application name [optional]
				@subject = 'Blocked process correction for application'
  			-- kill process
			SET @ExecStr = 'KILL '+CONVERT(varchar,@spid)
			-- %%% Comment if you do not wish to kill the blocking process
			EXEC (@ExecStr)
		END -- @spid IS NOT NULL
END -- block exists
SET NOCOUNT OFF
GO

PRINT 'Now create a scheduled job to EXEC sp_CheckForBlocking'
PRINT 'Suggest that the Job be scheduled for the same value in minutes as ' 
PRINT '  the value used above for the number of seconds a processes has '
PRINT '  been blocked, that way your worst case is a process is left '
PRINT '  blocking for less than twice this value'
PRINT ''
PRINT 'Finished Create Blocked Process script'
go

Rate

5 (2)

Share

Share

Rate

5 (2)