Get Active Locks

DROP FUNCTION dbo.udf_GetActiveLocks

GO

CREATE FUNCTION dbo.udf_GetActiveLocks (@DatabaseName SYSNAME)

RETURNS @ActiveLocks TABLE

(

RequestSessionID INT,

DatabaseName SYSNAME NULL,

ResourceType VARCHAR(100) NULL,

ResourceSubType VARCHAR(100) NULL,

ResourceDescription VARCHAR(100) NULL,

RequestType VARCHAR(100) NULL,

RequestMode VARCHAR(100) NULL,

RequestOwnerType VARCHAR(100) NULL

)

AS

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

Purpose: To retrieve details of all currently active lock

requests and the resources that requested 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

Author: Unknown

History: 7 May 2012 - Initial Issue

29 Jun 2012 - ChillyDBA - Converted to a table valued function

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

BEGIN

INSERT @ActiveLocks

(

RequestSessionID,

DatabaseName,

ResourceType,

ResourceSubType,

ResourceDescription,

RequestType,

RequestMode,

RequestOwnerType

)

SELECT

request_session_id AS RequestSessionID,

DB_NAME(resource_database_id) AS DatabaseName ,

resource_type AS ResourceType,

resource_subtype AS ResourceSubType,

resource_description AS ResourceDescription,

request_type AS RequestType,

request_mode AS RequestMode,

request_owner_type AS RequestOwnerType

FROM sys.dm_tran_locks

WHERE request_session_id > 50

AND resource_database_id = ISNULL(DB_ID(@DatabaseName), resource_database_id) -- for the current database only

AND request_session_id <> @@SPID -- screen out the session that this query is running in

ORDER BY request_session_id ;

RETURN

END