Find and Kill all the Blocked Process/Query

–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