SQL Server – SQL Scripts to find and Kill all the Blocked Process in a database

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

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)

Related Posts

, , ,