Monitor Blocking in SQL Server 2000 - Part II

Tuesday Oct 26th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

Part one of this series discussed how to obtain detailed information on blocking. Part two picks up with collecting detailed information when the blocking duration is longer than a minute.

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.



Step 5

Create a job Job_BlockMonitor by using Job_BlockMonitor.SQL, as shown in Fig 1.3 and Fig 1.4. This job will not create a schedule.



Click for larger image

Fig 1.3



Click for larger image

Fig 1.4



Source Code to create the job Job_blockmonitor

-- Script generated on 10/15/2004 1:41 PM
-- By: SQL\Administrator
-- Server: SQL

BEGIN TRANSACTION
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id
  FROM   msdb.dbo.sysjobs
  WHERE (name = N'Job_BlockMonitor')       
  IF (@JobID IS NOT NULL)
  BEGIN
  -- Check if the job is a multi-server job
  IF (EXISTS (SELECT  *
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''Job_BlockMonitor'' since there is already 
	  a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Job_BlockMonitor' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , 
    @job_name = N'Job_BlockMonitor', @owner_login_name = N'sa', 
	@description = N'No description available.', @category_name = N'[Uncategorized (Local)]', 
	@enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, 
	@notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, 
    @step_name = N'JOb_BlockMonitor', @command = N'usp_blockmonitor 1,0,''d:\BLOCKING.LOG''
', @database_name = N'master', @server = N'', @database_user_name = N'', 
    @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, 
	@retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, 
	@on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION
GOTO   EndSave
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

Download Job_BlockMonitor.SQL

Step 6

Create another job that runs the procedure usp_blockingalert every minute. (Refer to Fig 1.5, 1.6 and 1.7]


Fig 1.5


Fig 1.6


Fig 1.7

Source code for creating this job.

-- Script generated on 10/15/2004 1:51 PM
-- By: SQL\Administrator
-- Server: SQL

BEGIN TRANSACTION
  DECLARE @JobID BINARY(16)
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id
  FROM   msdb.dbo.sysjobs
  WHERE (name = N'Job_BlockMonitorAlert')
  IF (@JobID IS NOT NULL)
  BEGIN
  -- Check if the job is a multi-server job
  IF (EXISTS (SELECT  *
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''Job_BlockMonitorAlert'' since there is 
	   already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Job_BlockMonitorAlert' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , 
    @job_name = N'Job_BlockMonitorAlert', @owner_login_name = N'sa', 
	@description = N'No description available.', @category_name = N'[Uncategorized (Local)]', 
	@enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, 
	@notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, 
    @step_name = N'Job_BlockMonitorAlert', @command = N'exec usp_blockingalert 1, ''d:\Blocking.log''', 
	@database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', 
	@cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, 
	@output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, 
	@on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, 
    @name = N'Job_BlockMonitorAlert', @enabled = 1, @freq_type = 4, 
	@active_start_date = 20041015, @active_start_time = 0, @freq_interval = 1, 
	@freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, 
	@freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION
GOTO   EndSave
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

Download Job_BlockMonitorAlert.sql

Process

When blocking occurs, and if it is more than a minute, it will start the job "Job_BlockMonitor." "Jobs_MonitorBlock" will collect information instantly about blocking in the file "D:\Blocking.log" as shown in the Fig 1.8


Fig 1.8

Conclusion

Using this method, you can collect detailed information on blocking when a blocking duration exceeds one minute. If you would like to collect the information on blocking when the duration of blocking exceeds 2 or 3 minutes, then update the jobs schedule to run for every 2 minutes or three minutes.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved