Home Forums SQL Server 2005 T-SQL (SS2K5) How do I determine what process causes a lock with request_session_id = 0? RE: How do I determine what process causes a lock with request_session_id = 0?

  • 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[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?