New Post has been published on Varinder Sandhu 's Blog
New Post has been published on http://www.varindersandhu.in/2011/10/01/sql-server-sql-scripts-to-find-and-kill-all-the-blocked-process-in-a-database/
SQL Server – SQL Scripts to find and Kill all the Blocked Process in a database
<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->
SQL Server database administrators frequently need in especially development and test environments to find and kill all the blocked process. Following scripts are useful in that scenario.
Script – 1
-- Find All the Blocked Processes SELECT spid, status, loginame=SUBSTRING(loginame,1,12), hostname=SUBSTRING(hostname,1, 12), blk = CONVERT(char(3), blocked), dbname=SUBSTRING(DB_NAME(dbid),1, 10), cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
Script 2
-- Kill all the Blocked Processes of a Database DECLARE @DatabaseName nvarchar(50) -- Set the Database Name SET @DatabaseName = N'Datbase_Name' -- Select the current Daatbase -- SET @DatabaseName = DB_NAME() DECLARE @SQL varchar(max) SET @SQL = '' SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId and spid IN (SELECT blocked FROM master.dbo.sysprocesses) -- You can see the kill Processes ID -- SELECT @SQL --Kill the Processes EXEC(@SQL)
Similarly DBA can kill all the Processes of a Database as
-- Kill all the Processes of a Database DECLARE @DatabaseName nvarchar(50) -- Set the Database Name SET @DatabaseName = N'Datbase_Name' -- Select the current Daatbase -- SET @DatabaseName = DB_NAME() DECLARE @SQL varchar(max) SET @SQL = '' SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId -- You can see the kill Processes ID -- SELECT @SQL --Kill the Processes EXEC(@SQL)