Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a case where a query I'm running is taking a long time. When I checked sys.dm_exec_requests the blocking_session_id is a negative value, -2 to be specific. The wait is a LCK_M_X and the blocked command is a DELETE statement. To the best of my knowledge nothing else is touching that table at the moment. In fact the only active requests are the blocked connection and the query checking sys.dm_exec_requests. When I look at sys.dm_tran_session_transactions the blocked session is the only one showing up.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Take a look at this blog post by Bob Dorr: How It Works: Orphan DTC Transaction (Session/SPID = -2)

As Bob says, it's a bit of a misnomer that it's an orphaned transaction by DTC, but that is usually how it represents itself. It's due to (as per Bob's words) "NO ENLISTED SESSIONS on the SQL Server but the transaction is active yet". You should take a look at that link in it's entirety.

Oftentimes what happens with DTC transactions is that there is no commit/rollback and it does indeed pose a problem, as MSDTC may not reconnect to clean it up, release locks, etc.

See the BOL reference on sys.dm_tran_locks:

Distributed transactions that are not associated with a session ID value are orphaned transactions and are assigned the session ID value of -2. For more information, see KILL (Transact-SQL).

Yes, BOL uses the terminology "orphaned transaction", as in my experience that is what it usually ends up being (although you can't make that blanket statement without proper investigation that a distributed transaction coordinator hasn't indeed forgotten about the transaction holding everything up). In that case, after a proper orphaned determination, it is common to then KILL it based off of the unit of work (UOW).

share|improve this answer
1  
I was also able to find this article that helped me fix the problem: sqlservercentral.com/articles/Administration/99299 –  Kenneth Fisher Jan 22 at 21:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.