SQL 2005 System Objects for getting information about Transaction Locks


SQL 2005 System Objects for getting information about Transaction Locks

Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.
Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values ‘HOBT’, ‘Page’, ‘RID’  and ‘Key’. With this join we get the object_id of our locked table.
Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.
Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.
Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.
Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.
Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.
Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.

01.SELECT L.request_session_id AS SPID,
02. DB_NAME(L.resource_database_id) AS DatabaseName,
03. O.Name AS LockedObjectName,
04. P.object_id AS LockedObjectId,
05. L.resource_type AS LockedResource,
06. L.request_mode AS LockType,
07. ST.text AS SqlStatementText,
08. ES.login_name AS LoginName,
09. ES.host_name AS HostName,
10. TST.is_user_transaction as IsUserTransaction,
11. AT.name as TransactionName,
12. CN.auth_scheme as AuthenticationMethod
13.FROM sys.dm_tran_locks L
14. JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
15. JOIN sys.objects O ON O.object_id = P.object_id
16. JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
17. JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
18. JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
19. JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
20. CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
21.WHERE resource_database_id = db_id()
22. ORDER BY L.request_session_id


Please enter your comment!
Please enter your name here

15 + nineteen =