SQL Scripts to find and Kill all the Blocked Process/Query in a database

[Former Member]

SQL Server database administrators frequently need in especially development and test environments to find and kill blocked process. Following scripts are useful in that scenario.

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

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

Or You can use the following script

-- Script to kill all blocked processes

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

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)

--Identify the blocking query

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

 

-- Script to view all current processes / sessions on the server

select * from master.dbo.sysprocesses

Regards,

Marcelo Silva Santo