1

We have migrated our db from sql2000 win server 2k to sql2008 win server 2k8. We have linked server from sql2000 win server 2k. By our opinion the problem is with DTC and we have made a lot of setting that we found as solution for our problem, but still the problem exist. There is no any error or worning or information niether in the sql log nor in win event viewer. The application is hanging out and at the end the time out exception is shown.

What we have done till now:

  1. Enable Network DTC Access with inbound and outbound with No Authentication Required on win 2k8
  2. We have opened RPC dynamic port allocation through registry on 2k and 2k8
  3. We have entered subkey TurnOffRpcSecurity in the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC and made it enable on 2k and 2k8
  4. We have added exception for DTC in firewall for all entities

What we have notice that when we restart SQL service and make the first try for our transaction the following is shown: "Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required." and after it: "Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required."

Does someone have any idea what else can be done in order to solve the problem?

Thanks in advance.

Regards, Snezana

Improve this question
1
  • Try executing a query in management studio or sqlcmd using the linked server connection (and the same credentials as the application uses). If you can execute the query successfully, it's probably not the linked server connection. If there is something wrong with the linked server configuration, this should give you a more detailed error message.
    – DCNYAM
    Jan 13, 2010 at 20:41

4 Answers 4

Reset to default
1

Try running a trace with SQL Server Profiler on both servers while you try to perform the action in your application which has been resulting in the timeouts. That will show you exactly what is happening on the machines when the timeout happens.

And remember to keep an open mind in this process. I know you said that you think DTC is causing the problem, but in my experience, DTC doesn't usually cause timeouts. It either works or it doesn't, and the errors it throws when it isn't configured correctly explicitly say that DTC is not able to start the transaction. Linked server issues I've seen usually involve either a remote query that isn't finishing or a remote query that has to send back a lot of data to the calling server.

Improve this answer
1

Yes, you are right and run the profiler before but didn't notice some ascects becuase I was redirected with my mind that the problem is witht the linked server.

The problem was a deadlock and my advice for everyone that has timeout is first to check the occurance of deadlock.

Thanks.

Improve this answer
0

I have not tried exactly your combination. But here is something you could try:

  • Run dcomcnfg command (don’t tell me you couldn’t find the run command
  • Expand the “Component Services” node, then the “My Computer” node, then the “Distributed Transaction Coordinator” node
  • Right-Click on the “Local DTC” node and select “Properties”
  • On the “Local DTC Properties”, select the “Security” tab
  • Select Network DTC Access, Allow Inbound, Allow Outbound, No Authentication Required, and Enable XA Transactions

You could also view the MSDTC monitor while you are testing. That way you will see if the transactions are being created and then aborted, or not created at all.

Improve this answer
0

Are the two machine on the same subnet? I could never make DTC work across a router, firewall or no.

To debug, try using Microsoft's DTCPing and DTCTester (google for it).

Sometimes reboots are required when changing DTC settings, even though the system doesn't say so.

Improve this answer

You must log in to answer this question.