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 @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)