Get Blocking/Blocked Processes

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