August 15, 2012 at 2:18 am
My Service Broker is blocked by a lock that I can see in sys.dm_tran_locks. But how do I determine what process causes this lock that is held for several days already now? I've never before seen a request_session_id of 0 on locks?
select *
from sys.dm_tran_locks
where resource_description = '$hash = 0x2630942b:0xaf7f7df4:0xcc98c2'
output:
resource_type resource_subtype resource_database_id resource_description resource_associated_entity_id resource_lock_partition request_mode request_type request_status request_reference_count request_lifetime request_session_id request_exec_context_id request_request_id request_owner_type request_owner_id request_owner_guid request_owner_lockspace_id lock_owner_address
------------------------------------------------------------ ------------------------------------------------------------ -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------- ---------------- ------------------ ----------------------- ------------------ ------------------------------------------------------------ -------------------- ------------------------------------ -------------------------------- ------------------
METADATA CONVERSATION_GROUP 6 $hash = 0x2630942b:0xaf7f7df4:0xcc98c2 0 0 X LOCK GRANT 1 0 0 0 0 TRANSACTION 307015051 00000000-0000-0000-0000-000000000000 0x00000001DA5ACB10:1:1 0x00000001A8D42E00
(1 row(s) affected)
August 15, 2012 at 7:19 am
User the value here, request_owner_id , to look at sys.dm_exec_requests. From there you can join to sys.dm_exec_sql_text or whatever else you need to understand what's happening with that lock.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 16, 2012 at 1:28 am
Thanks Grant. Using the transaction id (from sys.dm_tran_locks.request_owner_id) I could figure out that the same transaction also had an IX lock on an internal table called "queue_messages_180220113", which is likely why service broker could not clean up any more closed conversations. What was keeping me from getting any more info was that in sys.dm_tran_session_transaction no line existed for the transaction id, so I could not determine what process was holding the lock. My assumption is that it was either an internal process or something orphaned.
Any way, I have now restarted the sql server and automatically the closed conversations are now cleaned again plus I could finally end the one conversation that was in 'ER' state. i.e. the problem is gone now, leaving me with a little unsatisfied feeling that I couldn't figure out what was going on. Thanks for your response, though.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply