Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remote login timeout not working Expand / Collapse
Author
Message
Posted Thursday, August 28, 2014 12:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:25 PM
Points: 128, Visits: 1,720
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
Post #1608372
Posted Friday, August 29, 2014 8:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:04 AM
Points: 44, Visits: 98
How are you calculating the query timeout as 27 secs?
Post #1608635
Posted Friday, August 29, 2014 8:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:25 PM
Points: 128, Visits: 1,720
I'm running the call in SSMS.


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

Don Urquhart
Post #1608640
Posted Tuesday, September 2, 2014 11:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:25 PM
Points: 128, Visits: 1,720
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
Post #1609720
Posted Tuesday, September 9, 2014 11:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:25 PM
Points: 128, Visits: 1,720
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
Post #1611906
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse