Get Blocking Locks

DROP FUNCTION dbo.udf_GetBlockingLocks

GO

CREATE FUNCTION dbo.udf_GetBlockingLocks (@DatabaseName SYSNAME)

RETURNS @BlockingLocks TABLE

(

RequestSessionID BIGINT,

DatabaseName SYSNAME NULL,

ResourceType VARCHAR(100) NULL,

ResourceSubType VARCHAR(100) NULL,

ResourceDescription VARCHAR(100) NULL,

ResourceAssociatedEntityID BIGINT,

RequestType VARCHAR(100) NULL,

RequestMode VARCHAR(100) NULL,

RequestOwnerType VARCHAR(100) NULL,

BlockingSessionID INT,

WaitType VARCHAR(100) NULL,

WaitDurationMS BIGINT

)

AS

/***************************************************************

Purpose: To retrieve details of all currently blocked lock

requests, the resources that requested them and the

session that is blocking them

Scope of the results is for the server unless a DatabaseName is provided

The following DMVs are used:

sys.dm_tran_locks - contains a record of all active locks on the server

dm_os_waiting_tasks - contains a record of all waiting tasks ( there may be many

tasks associates with one sesion)

Author: Unknown

History: 7 May 2012 - Initial Issue

1 July 2012 - ChillyDBA - Converted to a table valued function

****************************************************************/

BEGIN

INSERT @BlockingLocks

(

RequestSessionID,

DatabaseName,

ResourceType,

ResourceSubType,

ResourceDescription,

ResourceAssociatedEntityID,

RequestType,

RequestMode,

RequestOwnerType,

BlockingSessionID,

WaitType,

WaitDurationMS

)

-- Look for blocking

SELECT

tl.request_session_id AS RequestSessionID,

DB_NAME(tl.resource_database_id) AS DatabaseName,

tl.resource_type AS ResourceType,

tl.resource_subtype AS ResourceSubType,

tl.resource_description AS ResourceDescription,

tl.resource_associated_entity_id AS ResourceAssociatedEntityID,

request_type AS RequestType,

request_mode AS RequestMode,

request_owner_type AS RequestOwnerType,

wt.blocking_session_id AS BlockingSessionID,

wt.wait_type AS WaitType,

wt.wait_duration_ms AS WaitDurationMS

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.dm_os_waiting_tasks AS wt

ON tl.lock_owner_address = wt.resource_address

WHERE tl.resource_database_id = ISNULL(DB_ID(@DatabaseName), tl.resource_database_id)

ORDER BY wait_duration_ms DESC ;

RETURN

END