Get Blocking Locks

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/ChillyDBA

Date: 7 May 2012/1 July 2012

Description

Provides a summary of blocking lock information.

If a Database Name is supplied then the scope will be restricted to the database, otherwise all locks on the server will be returned.

Code

Function:

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

Test Code:

SELECT * FROM dbo.udf_GetBlockingLocks(NULL)

SELECT * FROM dbo.udf_GetBlockingLocks('AdventureWorks')