SQL Script to kill all blocked processes

Leave a comment (0) Go to comments

SQL Script to kill all blocked processes

-- ******************************************** */ -- Description */ -- ============ */ -- Script to kill all blocked processes */ -- =============================================*/ -- Compatibility : 2000+ */ -- ******************************************** */ 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) and 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) print @sqlstring IF @count = @max_count begin break end ELSE BEGIN select top 1 @count = spid FROM @spid_table where spid > @count end end


This script will kill all blocked process inside the SQL Server instance

EOF - SQL Script to kill all blocked processes, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.