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