Monitor Blocking in SQL Server 2000 - Part 1

October 20, 2004



Part of Database Administrator's job is to monitor blocking in SQL Server. By taking advantage of the Microsoft Knowledge Base article 271509, we can monitor the blocking and capture the SQL statements that are involved in the blocking to a file. This can be accomplished by updating the procedure from KB Article 271509 in such a way that it writes the output directly to a file.



Step 1



Create the procedure usp_writetofile, as shown in the figure Fig 1.1



Click for larger image

[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 code here.

Step 2

Create the procedure usp_blockmonitor, by executing the below SQL statement [Fig 1.2]. Download Code from here.


[Fig 1.2]

Step 3

Create a job Job_BlockMonitor by using Job_BlockMonitor.SQL as shown in Fig 1.3 and Fig 1.4. This job creates a schedule that causes this job to run every 5 minutes.


[Fig 1.3]


[Fig 1.4]

Source Code to create the job Job_blockmonitor

-- Script generated on 9/24/2004 10:37 PM
-- By: MAK
-- 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 job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Job_BlockMonitor', 
     @enabled = 1, @freq_type = 4, 
  @active_start_date = 20040923, @active_start_time = 0, @freq_interval = 1, 
     @freq_subday_type = 4, 
	 @freq_subday_interval = 5, 
  @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 Code here.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers