Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 26, 2004

Monitor Blocking in SQL Server 2000 - Part II

By Muthusamy Anantha Kumar aka The MAK

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


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.


Fig 1.2

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM