Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1549

Re: pitfalls of accessing linked server over WAN

Show all headers | View raw

> I tried it against a linked server while the remote box was down to simulate WAN down situation and it takes 50 sec to come back with messages
>
> OLE DB provider "SQLNCLI10" for linked server "REMOTEBOX" returned message "Login timeout expired".
> etc...
>
> That long wait is not acceptable for application, I'd like to know if we can possibly decrease that 50 seconds to 5 seconds, in our environment it is reasonable to conclude that link is down if connection is not obtained within 5 sec.
>
> I tried
> EXEC master.dbo.sp_serveroption @server=N'REMOTEBOX', @optname=N'connect timeout', @optvalue=N'5'
> EXEC master.dbo.sp_serveroption @server=N'REMOTEBOX', @optname=N'query timeout', @optvalue=N'5'
>
> but that had no effect on the long wait time for login.
>
> Are there any ways to do what I am trying to achieve?
>

Maybe this is your problem ...

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec917a47-0478-4d1b-8425-958a7f33effe/syssptestlinkedserver

"Unfortunately there is no way to control this. When you give a 
non-existant server name, SQL Server is trying to resolve the name of 
the server using Windows API - this means that depending on the network 
protocols and your network topology it can take several minutes. For 
example, DNS name resolution is more than 1 1/2 minutes.

Btw, you can use Windows Registry tweaks to control some of the timeout 
values. Search MSKB for more details."

Can you try it with IP instead of server name?

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-20 18:31 -0700
  Re: pitfalls of accessing linked server over WAN Erland Sommarskog <esquel@sommarskog.se> - 2013-08-21 07:20 +0000
    Re: pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-21 17:24 -0700
      Re: pitfalls of accessing linked server over WAN Erland Sommarskog <esquel@sommarskog.se> - 2013-08-22 08:48 +0000
  Re: pitfalls of accessing linked server over WAN Lutz <news@invalid.invalid> - 2013-08-21 10:02 +0200
    Re: pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-21 17:21 -0700
      Re: pitfalls of accessing linked server over WAN Lutz <news@invalid.invalid> - 2013-08-22 08:47 +0200
        Re: pitfalls of accessing linked server over WAN bradbury9 <ray.bradbury9@gmail.com> - 2013-08-22 00:10 -0700
        Re: pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-22 11:32 -0700

csiph-web