Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remote login timeout not working


Remote login timeout not working

Author
Message
Don Urquhart
Don Urquhart
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 1969
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?


If you don't stand for something, you'll fall for anything!,

Don Urquhart
WorkasDBA
WorkasDBA
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 122
How are you calculating the query timeout as 27 secs?
Don Urquhart
Don Urquhart
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 1969
I'm running the call in SSMS.


If you don't stand for something, you'll fall for anything!,

Don Urquhart
Don Urquhart
Don Urquhart
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 1969
I don't know if this is significant but the linked server are mostly SQL 2005 servers.

Any help would be greatly appreciated!


If you don't stand for something, you'll fall for anything!,

Don Urquhart
Don Urquhart
Don Urquhart
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 1969
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.


If you don't stand for something, you'll fall for anything!,

Don Urquhart
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search