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 20, 2004

Monitor Blocking in SQL Server 2000 - Part 1

By Muthusamy Anantha Kumar aka The MAK



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





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.



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


















Thanks for your registration, follow us on our social networks to keep up-to-date