Force MSDTC to orphan a SQLServer distributed transaction

When shutting down my project's WebSphere-based JavaEE application, distributed transactions are sometimes left alive in the SQLServer 2005 database. These retain their locks forever and must be killed manually. They are described generally in this very good article.

The article talks about how the transaction coordinator is unable to co-ordinate the distributed transaction participants and so flags the transaction with a process ID of -2.

Given that we get th开发者_如何学编程is issue infrequently, but that it always happens when we shut down the application, I am not convinced it is a failure of the MSDTC. It is more likely to have something to do with WebSphere and how it terminates applications.

I would like to test it. But I cannot reliably make it happen.

Is there any way to force the MSDTC to orphan a SQLServer distributed transaction?

---

My question was misleading, because I wasn't quite sure what the problem actually was. I suspect MSDTC is assisting WebSphere in transacting with MQ and SQLServer. The problem occurs when we perform an unclean shutdown of the WAS server and the vendor-provided part of our application tries to do some sort of cleanup and fails.

I suspect this question is not helpful to anyone else (unless they are using the Misys Message Manager in WebSphere on Windows) so apologies, dear reader, for wasting your time.

---

Orphaned DTC transactions can occur in case SQLServer sessions with open but not yet prepared DTC or XA transactions are closed. This may happen because of network interruption or due to unexpected application abort. In this case SQLServer keeps the transaction with DTC and the SPID is -2. The post on SQLServerCentral mentions that following query can be used to identify the orphaned transactions:

Select req_transactionUOW
 from master..syslockinfo
 where req_spid = -2

Exeuting "kill" with the UOW given as argument, will remove the active ransaction.

This behavior of SQLSever is very uncommon. Usually a session loss will abort any XA transaction.

The good news is that SQLServer provides an option that changes this behavior. The trace flag 3924 can be set in order to achieve this. Just exec DBCC TRACEON(3924,-1) with "sa" privileges and SQLServer automatically removes transactions in case sessions get lost.

The trace flag will be lost on SQLServer restart. In order to get it set during SQLServer startup automatically add it in SQLServer configuration manager:

SQLServer Configuration Manager sceenshot