Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Tuesday, May 23, 2017 4:58 PM
Hi everyone!
I am writing a T-SQL to query sys.dm_tran_locks but I notice the object names need to be read from different system tables depending on resource type (DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, or ALLOCATION_UNIT). May someone help me with this relationship? In other words, for each resource type, where should I look for a object name? "resource_associated_entity_id" column is not meaningful!
Hope I was clear enough.
Doria
Tuesday, May 23, 2017 6:24 PM ✅Answered | 1 vote
Hello,
Please look at below query:
SELECT dm_tran_locks.request_session_id,
dm_tran_locks.resource_database_id,
DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
CASE
WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
ELSE OBJECT_NAME(partitions.OBJECT_ID)
END AS ObjectName,
partitions.index_id,
indexes.name AS index_name,
dm_tran_locks.resource_type,
dm_tran_locks.resource_description,
dm_tran_locks.resource_associated_entity_id,
dm_tran_locks.request_mode,
dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id
Hope this helps.
Regards,
Alberto Morillo
SQLCoffee.com
Tuesday, May 23, 2017 5:22 PM
Can you post your current query? Did you try using OBJECT_NAME() function?
For every expert, there is an equal and opposite expert. - Becker's Law
Tuesday, May 23, 2017 7:09 PM
Thanks Alberto Morillo!
It really helps! By the way, what 'AND resource_database_id = DB_ID()' does?
Thanks.
Doria
Tuesday, May 23, 2017 8:25 PM
Hello,
It will filter only for the current database where you are running the query.
Hope this helps.
Regards,
Alberto Morillo
SQLCoffee.com
Tuesday, May 23, 2017 9:44 PM
Forget! I got it!!!
Thanks
Doria
Wednesday, May 24, 2017 5:57 AM
Hi dydoria,
Did you resolve your issue？ If so, please kindly mark the corresponding reply. It will help others who have the relevant requirement to find the answer more easily.
Thanks,
Xi Jin.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Wednesday, May 24, 2017 5:20 PM
Sure! It's done!
Doria
Wednesday, May 24, 2017 5:53 PM
On time,
Please let me share my T-SQL code and some questions:
--
SELECT
LEFT(A.resource_type, 50) AS resource_type,
LEFT(DB_NAME(A.resource_database_id), 50) AS database_name,
CASE
WHEN A.resource_type = 'OBJECT'
THEN LEFT(OBJECT_NAME(A.resource_associated_entity_id), 50)
WHEN A.resource_type IN ('PAGE', 'KEY', 'HOBT')
THEN LEFT(OBJECT_NAME(B.object_id), 50)
--WHEN A.resource_type = 'EXTENT'
--THEN LEFT(B.name, 50)
--WHEN A.resource_type = 'RID'
--THEN LEFT(B.name, 50)
END AS object_name,
CASE
WHEN A.resource_type IN ('PAGE', 'KEY', 'HOBT')
THEN LEFT(C.name, 50)
ELSE 'It''s not an index!'
END AS index_name,
LEFT(A.request_mode, 20) AS request_mode,
A.request_session_id AS SPID,
LEFT(X.login_name, 50) AS login_name,
X.login_time,
LEFT(X.host_name, 50) AS host_name,
LEFT(Y.client_net_address, 50) AS client_net_address
FROM sys.dm_tran_locks AS A
LEFT OUTER JOIN sys.partitions AS B ON A.resource_associated_entity_id = B.hobt_id
LEFT OUTER JOIN sys.indexes AS C ON B.object_id = C.object_id AND B.index_id = C.index_id
INNER JOIN sys.dm_exec_sessions AS X ON A.request_session_id = X.session_id
INNER JOIN sys.dm_exec_connections AS Y ON A.request_session_id = Y.session_id
WHERE A.resource_type IN ('OBJECT', 'PAGE', 'KEY', 'EXTENT', 'RID', 'HOBT'); -- DATABASE, FILE, ALLOCATION_UNIT, OBJECT, EXTENT, PAGE, KEY, HOBT, RID (heap), APPLICATION, METADATA.
GO
1. Why for some SPID it does not return login name?
2. I don't know where to look for a object name on RID and EXTENT resource types. May someone point me a direction?
Thanks.
Doria
Wednesday, May 24, 2017 6:23 PM
Hello,
You need to query the session DMV two times, one to look for the blocking user and another time to look for the blocked user.
The same article provides you the answer for the RID resource type.
Hope this helps.
Regards,
Alberto Morillo
SQLCoffee.com