共用方式為

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 https://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

Comments

  • Anonymous
    April 20, 2010
    The comment has been removed
  • Anonymous
    April 20, 2010
    The comment has been removed
  • Anonymous
    April 21, 2010
    You are killing a transaction that might be enlisted in another DTC manager so you need to determine the scope before killing them.
  • Bob Dorr

  • Anonymous
    October 06, 2011
    The comment has been removed

  • Anonymous
    September 04, 2013
    Guys, I agree with last post on this BLOG. If you have a UOW like this all zero's {00000000-0000-0000-0000-000000000000}. You can't do anything like Restore, Offline, Detach  (i.e., No exclusive access) on database. Even restarting the instance doesn't help. I can easily re-produce this from a backup. So how come a .bak file comes along with orphan UOW's. I wouldn't expect Microsoft to answer this.  Because there is no clear solution from Microsoft on this.  Guys if anyone has a solution, please post here. Thanks, Mubeen

  • Anonymous
    March 03, 2014
    The comment has been removed