August 28, 2014 at 12:12 pm
I am trying to test connection to linked servers using sp_testlinkedserver but, if the server is not accessible over our VPN it usually takes 27 seconds to timeout. Since we have nearly 100 linked servers I need to be able to test availability quicker.
I have changed the linked server properties CONNECTION TIMEOUT and QUERY TIMEOUT, as well as the local server option REMOTE LOGIN TIMEOUT, all to 5 seconds but it still takes 27 seconds to timeout. I have rebooted the server after making these changes and verified the change.
I am running on a 2008 R2 SQL instance and all linked servers are using the SQL Server Native Client 10.0 for there provider.
Is there something else I need to do to make this test quicker?
Don Urquhart
August 29, 2014 at 8:31 am
How are you calculating the query timeout as 27 secs?
August 29, 2014 at 8:41 am
I'm running the call in SSMS.
Don Urquhart
September 2, 2014 at 11:11 am
I don't know if this is significant but the linked server are mostly SQL 2005 servers.
Any help would be greatly appreciated!
Don Urquhart
September 9, 2014 at 11:36 am
I'm still hoping someone can help me with this.
Here is what i'm finding so far.
If I adjust the local servers Remote Login Timeout value, either up or down (tried 1 to 500 sec.s), it seems to have no effect on this whatsoever.
If I adjust the linked server Connection Timeout value it has an effect but not enough. No matter what low end value I use (tried 1 sec.) it still takes approx. the same time to error out. If I raise it (100 sec.s) it has a major effect - adds roughly the time that I set it to, usually just under the value.
Adjusting the Query Timeout value on the linked server also seems to have no effect, though I didn't really expect any.
The times that I get are usually in the 0:21 to 0:27 second range for a single server test, with the low end values seeming to come if I immediately repeat the test after a longer one, though I'm not positive on whether the difference due to a repeated test or just random chance due to other network traffic.
No matter what I do I have not been able to get the test to timeout in under 21 sec.s, which would cause major delays if very many of our VPN links were not available. Testing an available link usually is less than 1 sec.
Any suggestions would be greatly appreciated.
Don Urquhart
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply