sp_dba_checkblocked fix for sp_NotifyBlock

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.

Modified 12/31/01 by: Ed Pochinski email:sqlscripters@hotmail.com
visit www.sql-scripting.com
*/
/****** Object:  Stored Procedure dbo.sp_NotifyBlock******/
CREATE PROCEDURE sp_NotifyBlock --sp_dba_checkblocked
AS
 DECLARE @test int
set nocount on
-- Problems with error 512 more than 1 result returned due 
-- to the operator outside of the query. Found this when
-- tested with multiple blocks going on at once. Replaced 
-- with the conditional block below the commented 
-- conditional block and seems to work fine. 
/********************************************
IF (select datediff(mi,last_batch,getdate())
  from master..sysprocesses
 where blocked != 0 )> 5
********************************************//*******
This example detects blocks that are older than 5 minutes *********/-- This works well with a multitude of blocking at once
select @test = count(*)  
  from master..sysprocesses
 where blocked <> 0 and datediff(mi,last_batch,getdate()) >5 
 if (@test = 0) --no blocking will return 0 and exit
begin
return
  end
 else

BEGIN
EXEC master..xp_cmdshell "net send %mail_recipient% There Is a Block
on %server_name%", no_output
/**********************************************
This is where you want to call a proc to find the root blocker and dump to a temp table. Grab the spid and pass to a input buffer and or output buffer dump which can be piped to a .txt file if you schedule this as a job. Send a page with the spid and host name of lead blocker to let you know the event occurred via SMTP service. You will now have the proc or sql that caused the block and can hand this to the developer team to analyze.
***********************************************/
 
set nocount off
END
GO