Detect and Alert DBA of Blocking SPIDS

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