SQL Server – SQL Scripts to find and Kill all the Blocked Process in a database
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
» Read more