SQL Scripts to find all the Blocked Process/Query in a Database

Search FAQs

Issue
When SQL Server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes
request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block.
Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process
holds a transaction lock and doesn't release it.

Product Line
Struxureware Power Monitoring 7.0.x
Power Monitoring Expert 7.2.x, 8.x, 9.x, 2021, 2022
ION Enterprise 6.0. x
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R 2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017

Cause
Blocking occurs when one or more sessions request a lock on a resource, such as a row, page or table, but SQL Server cannot grant that lock because
another session already holds a non-compatible lock on that resource.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

In order to resolve a blocked process, we first need to determine which process is the blocking process and then if possible kill the blocking process.
To determine which process is the blocking process log into SQL Server Management Studio and run the attached SQL script (Blocking.sql).
Alternatively, execute the query(s) below :

Script to 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 to 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