Monitor Blocking in SQL Server 2000 - Part II

Part I of Monitor Blocking in SQL Server 2000 described how to get detailed information on blocking. Blocking is unavoidable in most types of applications. In this article, instead of collecting information every 5, 10 or 15 minutes, I am going to discuss how to collect detailed information about blocking when the blocking duration lasted more than a minute.

Step 1

Create a table using the script below. This table stores the "spid" and the last batch date and time information when a blocking occurs.

use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[BlockLastBatch]') 
   and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [BlockLastBatch]
GO
CREATE TABLE [BlockLastBatch] (
	[spid] [int] NULL ,
	[blocked] [int] NULL ,
	[last_batch] [datetime] NULL
) ON [PRIMARY]
GO

Download BlockLastBatch.sql.

Step 2

Create procedure usp_writetofile as shown in the figure Fig 1.1

Source Code

set quoted_identifier off
go
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_WriteToFile]') 
   and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_WriteToFile]
GO
CREATE PROCEDURE Usp_WriteToFile
(@FileName varchar(1000), @Text1 varchar(1000)) AS
--Objective: To Write a given string on to a given file
--Created by: MAK
--Date: Sep 25, 2004
DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Error: Scripting.FileSystemObject'

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,
@FileName, 8, 1
IF @OLEResult <>0 PRINT 'Error: OpenTextFile'

--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'Error : WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

Download usp_writetofile.sp

Step 3

Create the procedure, usp_blockmonitor, by executing the below SQL statement [Fig 1.2].

Download usp_blockmonitor.sp.

Step 4 Create another procedure for identifying the blocking duration.

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Create procedure usp_blockingalert 
@thresholdmin int = 1,
@applicationlog varchar(1000) = 'd:\BLOCKING.LOG'
--Created by MAK
--Created date: Oct 14, 2004
--Objective: To run BlockMonitor Job when blocking is more than a minute
as
declare @count1 int
declare @runstatus int
declare @date varchar(100)
declare @error varchar(1000)

select @count1 =count(*) from 
master..BlockLastBatch a,
(select spid,blocked,last_batch from master..sysprocesses (nolock) where blocked<>0) as b
where a.last_batch = b.last_batch

if @count1 > 0
begin
set @error = 'Blocking persist more than ' + convert(varchar(10),@thresholdmin )
exec master..usp_writetofile @applicationlog , @error
--print 'Blocking more than one minute '
select @runstatus =run_status from msdb..sysjobhistory (nolock) where job_id =
(select job_id from msdb..sysjobs (nolock) where name ='Job_BlockMonitor')

if @runstatus <>4 
begin
exec msdb..sp_start_job 'Job_BlockMonitor'
end
end

truncate table master..BlockLastBatch 
insert into master..BlockLastBatch 
select spid,blocked,last_batch from master..sysprocesses (nolock) where blocked<>0



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Download usp_blockingalert.sql.