How It Works: Orphan DTC Transaction (Session/SPID = -2)

It looks like it would be a good post to help clarify that -2 does NOT mean ORPHAN.

_____________________________________________________________________________________

CURRENT EXCHANGE
_____________________________________________________________________________________

From: Robert Dorr
Sent: Tuesday, April 20, 2010 8:47 AM
Subject: RE: ONSITE:Orphaned Distributed Transactions

 

Let me clarify the term Orphaned. A -2 is not Orphaned it means there are NO ENLISTED SESSIONS on the SQL Server but the transaction is active yet. Let me give you can example.

Begin DTC Transaction with DTC Transaction Manager

Connect To SQL and enlist SPID 50 - Transaction imported to SQL Server and communications established with the DTC Manager and session enlisted

T-SQL work done on SPID

Disconnect Session – Transaction still tracked by SQL to hold locks and such but no session enlisted so reporting now shows (-2)

               

What you have is an application that has done work in a DTC transaction against SQL and not committed or aborted it until a later point in time.

Sent: Tuesday, April 20, 2010 8:38 AM
Subject: ONSITE:Orphaned Distributed Transactions

 

The cx is seeing request_session_id of -2 in sys.dm_tran_locks and req_spid in sys.syslockinfo.

After the application of CU9 of SP2 they saw reduced occurrences of the orphaned tx. Now they are seeing transient -2’s that seem to clear themselves away.

Is this expected behavior? All documentation I could find seemed to indicate a -2 was an orphaned distributed transaction. Has the behavior changed to automatically ‘sweep’ the -2 away?

_____________________________________________________________________________________

PREVIOUS EXCHANGE
_____________________________________________________________________________________

From: Robert Dorr
Sent: Monday, June 09, 2008 2:35 PM
Subject: RE: SPID = -2

 

No, just bad wording. SQL allows the DTC transaction to remain active as long as the DTC manager has the transaction active but it does not require a session.

Sent: Monday, June 09, 2008 2:28 PM
Subject: RE: SPID = -2

 

Thanks for the correction. In the BOL topic for the KILL command (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/071cf260-c794-4b45-adc0-0e64097938c0.htm) it says:

Use KILL UOW to terminate orphaned distributed transactions. These transactions are not associated with any real session ID, but instead are associated artificially with session ID = '-2'. This session ID makes it easier to identify orphaned transactions by querying the session ID column in sys.dm_tran_locks, sys.dm_exec_sessions, or sys.dm_exec_requests dynamic management views.

Would this be just an instance of unclear/incomplete documentation? Do you have a better reference on this topic?

From: Robert Dorr
Sent: Monday, June 09, 2008 3:20 PM
Subject: RE: SPID = -2

 

Be careful (-2) does not mean orphaned. It means you have an open DTC transaction managed by an external ITransaction interface but no Sessions currently using it.

Sent: Monday, June 09, 2008 2:13 PM
Subject: RE: SPID = -2

 

Yes, -2 is the SPID for orphaned MSDTC sessions. You may be able to get additional information about the process from the following DMVs:

sys.dm_tran_locks

sys.dm_exec_requests

sys.dm_os_waiting_tasks

sys.dm_tran_active_transactions

Another troubleshooting step is to enable MSDTC tracing as described in http://support.microsoft.com/kb/899115/en-us.

Sent: Monday, June 09, 2008 2:01 PM
Subject: SPID = -2

 

 

We have a query in a BizTalk process that it’s being blocked by a process with SPID = -2.

 

There is no process listed with a negative SPID.

 

What does SPID -2 mean and how can we find more information about the blocking process?

 

I have found comments about SPID -2 being related to DTC transactions. It’s that the only case where spid -2 is used? What’s the best practices recommended to deal with this type of issue?

Bob Dorr - Principal SQL Server Escalation Engineer