refresh linked server?

[ideasworking]

Hello,

Has anyone come up against a problem with an SQL 2000 server losing it's connection to a linked server even though the linked server is healthy? I have this problem. It seems every once in awhile the connection between the servers times out, hiccups, or something and then I have to reboot the SQL server to establish the connection to the linked server.

In my configuration I pull data once a minute from the linked server. If the transfer fails I would like to refresh the link to the linked server.

Anyone have a suggestion to restore/refresh the linked server?

Thanks,
Lou

 

[Catadmin]

First, I would check the health of the network. It sounds like that might be the problem.

When you start having issues, PING the other server from a command prompt and use TraceRt to see how far the packets are actually going. Ask your network admin to put a sniffer on the box and see if maybe you have a failing NIC or other problem. Also ask the server admin to verify you don't have a loose wire on either server.

Your problem may be infrastructure / hardware related rather than SQL Server related.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ideasworking]

Hi,

Thanks for the response. This is a small facility with a dozen employees. I am the pseudo network administrator, application developer, DBA, help desk, electrician, controls engineer, and so on... so Tek-tips is my IT support.

I don't see the network being much of a problem. There is only one hop to the linked server. Time is < 1ms.

I suspect that the problem is related to something like a timeout for responses. The linked server is a historian which polls 3000 points every 500 milliseconds. I'm guessing sometimes it might not respond to a request immediately.

Is there something like a time out setting for linked servers? Knowing more about the linked server, can amyone think of other reasons why the link might be dropped?

I doubt there is much I can do to prevent the link from dropping out. So back to my original question... how can I refresh the link to the linked server?

TIA,
Lou