January 13, 2010 at 11:26 am
Hi,
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
January 13, 2010 at 11:40 am
I don't know if this will help much, but maybe to narrow the problem (and hopefully someone else will jump in):
--Try querying a table you know doesn't exist - see if it throws an error or still times out. If it throws an error, then it knows the table does not exist and is at least connecting to the server. If it still times out, the connection isn't happening at all.
--Try querying a single table (no joins) but put WHERE 1=0 in the query. This should run very fast and just return an empty result set (but you will see the column names). If that works, then the query you trying to run isn't working very efficiently. I've seen linked servers request one row at a time, or query a remote table once for each local row in a join which just kills performance (there are a few ways to fix this too).
--Try creating a linked server to a different server (if you have one available) both from the 2008 box and the 2000 box. At least then you can narrow it down to which box (or both) is causing the issue.
None of these will fix the issue, but might help narrow down what is causing it.
Chad
January 13, 2010 at 8:41 pm
Hi,
Also, I would add that we have tried to execute the query:
begin distributed tran
exec linkedserver.linkdb.storeprocedure
commit tran
and it is working.
And also on another places where we have transaction on linked server is is working. And sometimes it is happening the targeted place to pass and there is no rule when that will be.
Any idea what else to check?
January 14, 2010 at 8:46 am
Cool - that is good news. I would think that means that the linked server and DTC are working. If the linked server wasn't working, the SP wouldn't execute and if DTC was not up, I believe (but I'm not positive) you would get an error when the proc was executed inside a transaction, but not when it was executed on it's own.
My guess is that your query is using data both locally and remotely, correct? Here are some things to try:
First, run profiler on the remote server and watch for the query coming from the local server. Make sure you have RPC: Starting, SP:Starting, SQL:BatchStarting, and SQL:StmtStarting selected as events and all the available columns (but particularly TextData). That's overkill I'm sure, but I know then you'll see it if it is working. The linked server will change the query slightly (it will alias table and column names), so search the TextData field for something unique in the where clause or the login name the linked server is using. I'm guessing you'll find that the query is starting on the remote server, but not finishing (meaning it is an optimization issue and not a configuation or connection issue). This will be easiest if the system is "quiet" and no one is using the remote server, but that might not be possible if it is in production.
Can you split your query into two pieces - one select that pulls all the data you need from the remote server into a temporary table, and a second that joins the results to the local data?
For example, instead of something like this:
SELECT columnA, columnB, columnC
FROM remoteserver.database.schema.remotetableA
INNER JOIN remoteserver.database.schema.remotetableB on remotetableA.ID = remotetableB.ID
INNER JOIN localtableC on remotetableB.ID2 = localtableC.ID2
Try somthing like this:
CREATE TABLE #temp1 (columnA int, ColumnB int, ID2 int)
INSERT INTO #temp1
SELECT columnA, columnB, ID2
FROM remoteserver.database.schema.remotetableA
INNER JOIN remoteserver.database.schema.remotetableB on remotetableA.ID = remotetableB.ID
SELECT columnA, columnB, columnC
FROM #temp1 temp1
INNER JOIN localtableC on temp1.ID2 = localtableC.ID2
I've found that splitting the local and remote portions sometimes makes a big difference in speed.
Chad
January 14, 2010 at 10:46 am
Hi,
We have solved the problem.
Silly, but the problem was not with DTC not with linked server but deadlock 🙂
Thanks for your effort to help us.
I would advice everyone that has problem which result is TimeOut to check for deadlock.
Regards, Snezana
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply