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