Skip to main content

Posts

Showing posts from March, 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 Begin select @i = blocked from master..sysprocesses where spid = 113 if isnull(@i , 0) <> 0 BEGIN set @bCont = 1 print @s SET @s = 'kill ' + convert(varchar(12), @i) exec (@s) waitfor delay '00:00:00.05' END else SET @bcont = 0 end