Technical Article

sp_dba_checkblocked fix for sp_NotifyBlock

,

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. It worked fine when there was only 1 block. Depending on the type of server there can be a "whole lot of blocking going on "Replaced with the conditional block below the commented

conditional block and seems to work fine. Also added some ideas I've implemented for additional funtionality.

Ed Lindenhofen deserves all the credit for his idea I just improvised and built on his idea.

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating