Wednesday, March 25, 2009

Killing Excessive Blocking Transactions in SQL

I recently made a small error in a SQL Server parsing function that caused an endless loop. Unfortunately SQL doesn't escalate an error and will allow these transactions to go on endlessly.

I quickly realized the error and attempted to correct the SQL function. However since there were users stuck in this endless loop, my ALTER FUNCTION transaction was being blocked.

KILLing individual SPIDs didn't work since I could not keep up with the number of backup of user requests.

To circumvent this, I ran a process to KILL all queries blocking my SPID:

declare @bCont bit, @i int, @s varchar(100)
set @bCont = 1

while @bCont = 1
select @i = blocked
from master..sysprocesses
where spid = 113

if isnull(@i , 0) <> 0
set @bCont = 1
print @s
SET @s = 'kill ' + convert(varchar(12), @i)
exec (@s)
waitfor delay '00:00:00.05'
SET @bcont = 0

Share Links