Detect and Alert DBA of Blocking SPIDS

,

This script will detect and alert the DBA of blocking processes. The DBA can adjust the length of time the block has exisited before sending the net send message.

DROP PROCEDURE sp_NotifyBlock
go
/* 	Ed Lindenhofen 08/25/2001
	ELinden11@Home.com
	input:	None
	output:	Net Send Message
	Desc:	List blocking SPIDS which have existed for a period specified by the                 administrator.
	Warnings: None.
*/

/****** Object:  Stored Procedure dbo.sp_NotifyBlock******/

CREATE PROCEDURE sp_NotifyBlock 
AS
BEGIN
set nocount on
 
IF (select datediff(mi,last_batch,getdate())
  from master..sysprocesses
 where blocked != 0 )> 5
/*******This example detects blocks that are older than 5 minutes *********/
	BEGIN
		EXEC master..xp_cmdshell "net send %mail_recipient% There Is a Block
on %server_name%", no_output
	
	END
set nocount off
END
GO

Rate

Share

Share

Rate