Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

scenario: Windows 2003 sp2 x64 enterprise edition. SQL 2005 sp2 cu9 x64 Enterprise edition

After restarting the resource groups on 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."

MSDTC is fine since the other 3 function normally. The only way to "fix" it is to take the 4th instance offline and bring it online again. Is there any way to fix this enlistment without restarting?

share|improve this question

1 Answer

up vote 0 down vote

Is MSDTC configured to work through a firewall by chance?

share|improve this answer
No firewall, Com properties for port ranges is blank. The interesting thing is that if I issue the command: "begin distributed tran" locally on the first 3 instances I get a success message with the clusters msdtc resource. On the fourth instance I get the enlist error. Moving the msdtc resource to the other node does nthing for the fourth instance. Only a restart of that instance allows the instance to enlist. – seagull surfer Aug 14 '09 at 23:10
Very strange. Normally that'll be caused because MSDTC runs out of TCP ports because it's been restricted to go through a firewall. If you open the Component Services does it show anything failed? When SQL Starts saying that it failed is it able to use distributed transactions? I take it this only happens when you restart all 4 resource groups at the same time? – mrdenny♦ Aug 15 '09 at 2:15
Hrmm... I looked in component services but didn't look too closely. Unfortunately it's running now and there are no errors. It is possible that starting all 4 instances consumes all the ports. I'll have to check out the port usage next time. The SQL server that throws the error cannot run any distributed transactions, but will after a restart. I haven't focused on MSDTC because it was working on the other 3 instances. Odd though... if the ports were all used up then why would the other instance on that node work properly. I'll have to collect more info the next time it happens. – seagull surfer Aug 17 '09 at 15:46
SQL takes persistent connections to MSDTC. So if its out of ports the other instances can still work fine, until they need more ports then they currently have. SQL will eventually drop most if not all of those connections. – mrdenny♦ Aug 17 '09 at 20:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.