Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to kill the top blocker and report what it was doing. Expand / Collapse
Author
Message
Posted Thursday, September 27, 2007 9:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 18, 2009 8:36 AM
Points: 5, Visits: 21
Comments posted to this topic are about the item Script to kill the top blocker and report what it was doing.


http://www.Integer.org
Post #403577
Posted Wednesday, October 27, 2010 9:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 28, 2012 12:03 PM
Points: 2, Visits: 28
I got this error message when trying to run this script:

(1 row(s) affected)

(0 row(s) affected)
Msg 512, Level 16, State 1, Line 18
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Process Complete...

it error on this line
IF @blocker not in (select spid from sysprocesses where blocked != 0)

The syntax is correct, not using any of the =, !=, <, <= , >, >= operator.

When the script do not find any blocked on the beginning of the script
use master
go
select getdate()
go
-- Report on blocking spids to be terminated.
select blocked as "Blocker"
,count(*) as "Victim Count"
from master..sysprocesses
where blocked > 0
group by blocked

it did not return error.

Does any one knows what cause the script to fail? I will appreciate any comment.

Paulus
Post #1011638
Posted Wednesday, October 27, 2010 2:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 28, 2012 12:03 PM
Points: 2, Visits: 28
I added the max expression and fix it.

AND (select max(datediff(ss,last_batch,getdate()) ) FROM sysprocesses where blocked = @blocker) > 5
Post #1011870
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse