Technical Article

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