Get Blocking/Blocked Processes
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Unknown
Date: 2 Aug 2012
Description
Provides a summary of blocked processes and the processes that are blocking them.
If a Database Name is supplied then the scope will be restricted to the database, otherwise all blocked processes on the server will be returned.
Code
Function:
DROP FUNCTION dbo.udf_GetBlockingProcesses
GO
CREATE FUNCTION dbo.udf_GetBlockingProcesses (@DatabaseName SYSNAME)
RETURNS @BlockingProcesses TABLE
(
DatabaseName SYSNAME NULL,
BlockedSPID INT,
BlockedQuery VARCHAR(MAX),
BlockedResource VARCHAR(100),
BlockingSPID INT,
BlockingSQL VARCHAR(MAX),
DATETIME DATETIME
)
AS
/***************************************************************
Purpose: To retrieve details of all currently blocked
processes and the processes that are blocking them.
Also includes the TSQL being run by the both processes
The following DMVs are used:
sys.dm_exec_connections - contains a record of all connections on the server
sys.dm_exec_requests - contains a record of all active requests on the server
sys.dm_exec_sql_text - contains the sql text being run by each request
Author: Unknown
History: 19 Aug 2009 - Initial Issue
1 Aug 2012 - ChillyDBA - converted to TVF and extended to return the parent queries too.
****************************************************************/
BEGIN
INSERT @BlockingProcesses
(
DatabaseName,
BlockedSPID,
BlockedQuery,
BlockedResource,
BlockingSPID,
BlockingSQL,
DATETIME
)
-- get the blocked processes and the process that is blocking
SELECT
DB_NAME(Blocked.database_id) AS DatabaseName,
Blocked.Session_ID AS BlockedSPID,
Blocked_SQL.TEXT AS BlockedQuery,
Waits.wait_type AS BlockedResource,
Blocking.Session_ID AS BlockingSPID,
Blocking_SQL.TEXT AS BlockingSQL,
GETDATE() AS DATETIME
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocked.Blocking_Session_ID = Blocking.Session_ID
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON waits.Session_ID = Blocked.Session_ID
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS Blocking_SQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS Blocked_SQL
WHERE Blocked.database_id = ISNULL(DB_ID(@DatabaseName),Blocked.database_id)
RETURN
END
Test Code:
SELECT * FROM dbo.udf_GetBlockingProcesses (NULL)
SELECT * FROM dbo.udf_GetBlockingProcesses ('master')