CODEWITHSUNDEEP
sqlsql-serverkillkill-process
anonymous1110
anonymous1110

Reputation: 885

SQL script to kill all blocked processes?

I'm trying to create an SQL script that will kill all blocked process.

Am I missing something? 

declare @max_count int, @count int, @sqlstring varchar(100)
declare @spid_table table (spid int NOT NULL)

INSERT @spid_table
select spid
from master.dbo.sysprocesses
where spid in (select blocked from master.dbo.sysprocesses where blocked > 0) 

select @max_count = MAX(spid) FROM @spid_table
select top 1 @count = spid from @spid_table

while @count <= @max_count
begin
select @sqlstring = 'kill ' + CONVERT(varchar(4), @count)
exec(@sqlstring)
end

Upvotes: 2

Views: 13193

Answers (2)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

You are missing a way to increment through your @spid_table. You should add two lines inside your while after the exec

DELETE FROM @spid_table WHERE spid = @count
SELECT @count = spid FROM @spid_table

or add IDENTITY column to @spid_table and use it for loop.

Upvotes: 1

Devart
Devart

Reputation: 121922

Try this one -

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT CHAR(13) + 'KILL ' + CAST(spid as VARCHAR(5))
    FROM master.dbo.sysprocesses
    WHERE blocked != 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

PRINT @SQL

EXEC sys.sp_executesql @SQL

Upvotes: 7

Related Questions