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