CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

  • Comments 6

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

Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post
  • We normally kill these processes if they are blocking other processes (with different spids). Do you recommend they we dont kill these at all?

  • We normally kill these processes if they are blocking other processes (with different spids). Do you recommend they we dont kill these at all?

  • 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

  • Although one must be careful about other parties in the distributed transaction, say you want to roll back the transaction.

    What happens when the request_owner_guid is a 0'd GUID? ie: 00000000-0000-0000-0000-000000000000

    Can't kill it. Can't abort it from MS DTC. Can't commit. Can't forget...

    Only solution I've found is to restart the SQL server.

  • 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

  • Ok, I will concede your semantics that an SPID -2 is a disconnected tx not an orphaned tx.  But this disconnected session causes locks to be held and serious performance problems.  It seems that the only solution is to manually clear the SPID -2.  I don't see any way to reattach a session to the tx,  This disconnected session is causing a great deal of customer complaints for the product I support.  

       Is there a better solution than running the Kill command on it.

    msdn.microsoft.com/.../ms173730.aspx

Page 1 of 1 (6 items)