Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION)
You may run into an issue when running JDBC XA transactions against SQL Server 2008 on Windows Server 2008 or R2 Cluster. The error in the SQL error log is similar to this:
Error: 8509, Severity: 16, State: 1
Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION).
2010-01-20 13:18:35.03 spid72 Error: 8509, Severity: 16, State: 1.
2010-01-20 13:18:35.03 spid72 Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION).
The same JDBC application works fine when SQL Server is on Windows 2003 Cluster.
The problem is related to MS DTC changes in Windows Server 2008 Cluster. You can configure multiple MS DTC instances on Windows Server 2008 or R2 Cluster rather than just one MS DTC instance as on Windows 2003 Cluster. The clustered SQL Server resource group can have its own MS DTC instance, which could be different from the cluster default DTC instance.
SQL Server loads SQLJDBC_XA.DLL which is a legacy application that is not aware of any DTC changes on Windows Server 2008 or R2. A legacy application uses the cluster default DTC instance if no mapping for it exists. SQLJDBC_XA requests an XA transaction in the cluster default DTC instance. The transaction will be imported later by the SQL Server. SQL Server 2008, on the other hand, is a new application that uses the new parameters available in DtcGetTransactionManagerEx to locate the DTC instance. It picks up the DTC instance in the same resource group as itself when it attempts to import the transaction. This DTC instance could not find the transaction because the transaction was created in the cluster default DTC instance that is a different DTC instance.
You could see the same issue with other JDBC XA drivers as well. To resolve the issue use TMMapping to map the SQL Server to the DTC instance in the same SQL group. This ensures SQLJDBC_XA and SQL point to the same DTC instance. SQL Server 2005 doesn’t use the new parameters in DtcGetTransactionManagerEx so the problem doesn’t happen there.
To set up a mapping between SQL and DTC, use msdtc.exe as follows:
msdtc.exe -tmMappingSet -name -service <full clustered SQL service name> -clusterResourceName <DTC resource name>
e.g.
msdtc.exe -tmMappingSet -name DTC_SQL01_Mapping -service “MSSQL$DEVSQL01” -clusterResourceName "DTC-DEVSQL01"
To view the mapping, use:
msdtc.exe -tmMappingView -name DTC_SQL01_Mapping
To view all known mappings, use:
msdtc.exe -tmMappingView *
The clustered SQL service name is the service name but NOT the display name. The service name can be found in Services MMC. You can find the DTC resource name in Failover Cluster Manager.
There is a small thing I want to mention which could save you a lot of time. If you copied the tmMappingSet command from somewhere such as Outlook or Notepad, the command may not work. No error is reported in the output. However if you look at the application event log you will notice an error was logged:
Invalid command line arguments.
If that happens, simply remove all '-' and retype them back. It has something to do with encoding format.
Comments
Anonymous
March 12, 2015
Hi, We have a 4 node Active-Active SQL cluster, I am installing 4 instances of DTC one per SQL Role and i am going to run Msdtc –tmMappingSet, Do i need to run the tmMappingSet on all the 4 nodes for each instance of DTC?Anonymous
March 12, 2015
Hi Vinoth, If you are not using SQL JDBC XA, you do not need to set TM mappings. SQL 2008 Server knows which clustered DTC instance it needs to use. TM mappings are stored in the cluster registry which is shared across all nodes. You can set them at any cluster node. blogs.technet.com/.../how-to-configure-multiple-instances-of-distributed-transaction-coordinator-dtc-on-a-windows-server-failover-cluster-2008.aspxAnonymous
June 24, 2016
Hi,What is thre rollback script, If I want to un-mapping between SQL and DTCthank you!