Monitor Blocking in SQL Server 2000 – Part II

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

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.