Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I determine what process causes a lock with request_session_id = 0? Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 2:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:20 AM
Points: 411, Visits: 1,400
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)





Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1345160
Posted Wednesday, August 15, 2012 7:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 15,664, Visits: 28,062
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1345261
Posted Thursday, August 16, 2012 1:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:20 AM
Points: 411, Visits: 1,400
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.




Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1345699
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse