DMV Queries - Get Active 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:        5 May 2012/29 Jun 2012

Description

Provides a summary of active 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_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


Test Code:

SELECT * FROM dbo.udf_GetActiveLocks(NULL)

SELECT * FROM dbo.udf_GetActiveLocks('Master')


ċ
udf_GetActiveLocks.sql
(2k)
Andy Hughes,
Jun 29, 2012, 12:15 PM
Comments