Script to kill the top blocker and report what it was doing.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by integer » Script to kill the top blocker and report...

Script to kill the top blocker and report what it was doing.

Rate Topic

Display Mode

Topic Options

Author

Message

integer

Posted Thursday, September 27, 2007 9:25 AM

Forum 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

PaulusTandang

Posted Wednesday, October 27, 2010 9:17 AM

Forum 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

PaulusTandang

Posted Wednesday, October 27, 2010 2:21 PM

Forum 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 »

Permissions