August 14, 2009 at 4:01 pm
scenario: 2 Nodes Windows 2003 sp2 x64 enterprise edition. Clustered SQL 2005 sp2 cu9 x64 Enterprise edition
After restarting the resource groups on the two node active-active cluster, 3 SQL 2005 instances start up fine. The 4th one starts up but starts throwing the following error.
Enlist operation failed: 0x8004d00e(XACT E NOTRANSACTION). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.
All the other instances can handle the command "begin distributed tran" but the 4th one throws this error immediately that it cannot enlist a transaction.
MSDTC is fine since the other 3 sql instances function normally.
Just to test it, I took msdtc offline and brought it online and even moved the group to the other node, but still the 3 instances work fine and the 4th says it cannot enlist msdtc. The only way I can "fix" it is to take the 4th instance offline and bring it online again.
Is there any way to fix this enlistment without restarting the instance?
August 17, 2009 at 10:03 am
Distributed transactions were happily running between our SQL servers and then we installed Windows 2003 Server SP2 and all of a sudden we got this nice error message when attempting a remote data update via linked servers. I couldn't even run "BEGIN DISTRIBUTED TRANSACTION" without getting the error message. The full error message is:
"Enlist operation failed: 0x8004d00e(XACT_E_NOTRANSACTION). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MSDTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager."
Apparently, SP2 disables network DTC access (how nice, I feel more secure knowing that my data can't be updated remotely anymore).
Solution:
From Control Panel | Add or Remove Programs | Add Remove Windows Components
Select "Application Server" and click the "Details" button
Check "Enable network DTC access" and click OK for however many prompts it takes to apply the changes.
****NOTE**** this change WILL SHUT DOWN SQL SERVER so plan accordingly (this would have been handy to know in advance since I ran it in the middle of the workday and everyone got disconnected).
Hope this helps!
Oraculum
August 17, 2009 at 10:10 am
Have you reviewed the configuration of DTC itself. And since you mention clustering, were they all running on the same server before and now they are seperated. When I setup clustering I had to assign DTC to the resource group with my SQL server, but I think that was because of disk.. Tell us more about your environment.
CEWII
August 17, 2009 at 10:17 am
Network DTC access is not disabled. The other 3 nodes on the cluster communicate just fine to the MSDTC resource on the same cluster.
August 17, 2009 at 10:29 am
The configuration of dtc looks fine. The other 3 nodes communicate to it without problem and can run distributed transactions. I tried moving the msdtc back and forth between nodes and it has no effect on the 4th node. Only after the 4th sql server instance is restarted can it run a distributed transaction. My theory is this:
When all the instances on the cluster are restarted the ports avaiable for MSDTC are all consumed and when the final instance trys to establish a link to msdtc it fails and the the link up from the instance to MSDTC is never attempted to be established again. So even later when all the ports are free the transactions fail because the main linkup to MSDTC is only attempted at the instance startup. From what I read there is no way to reset this linkup or trouble shoout it's process from SQL server.
I will have to collect more info for component services and msdtc trace next time this happens.
August 17, 2009 at 11:54 am
The SQL Errorlog usually references DTC, what does it have to say about DTC in the SQL start that had problems?
CEWII
August 17, 2009 at 1:05 pm
These first two messages occur once during startup:
Message
Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
Message
Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
Then this message just repeats itself:
Message
Enlist operation failed: 0x8004d00e(XACT_E_NOTRANSACTION). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.
August 17, 2009 at 1:18 pm
Well unfortunately that didn't help at all.. Have you had the DTC monitor open when a transaction was attempted, do you see the numbers go up for an attempted (yet aborted) transaction?
CEWII
August 17, 2009 at 1:43 pm
Good point. That would tell if it was connecting at all. Next time it happens I'll spend more time looking at the component services monitoring for dtc. Unfortunately, for trouble shooting, the instance is now speaking to msdtc without error.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply