1

I need to know where to set the timeout for a login or a query to a linked server. If the server is down I don't want to waste too much time waiting for a timeout. (Our servers are on the same network with a gigabit connection. All are running SQL Server 2017.)

For example, SELECT * FROM SERVER_A.master.sys.databases

runs instantaneously if the linked server is up. If the linked server is down, e.g. if I stop the SQL Server service on SERVER_A, it takes 21 seconds to timeout with the following message:

OLE DB provider "SQLNCLI11" for linked server "SERVER_A" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "SERVER_A" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 2, Level 16, State 1, Line 2
Named Pipes Provider: Could not open a connection to SQL Server [2].

EXEC sp_testlinkedserver SERVER_A times out in the same 21 seconds with the same message.

I can't figure out where the timeout is set. Here is what I've tried:

  • Linked Server Properties for SERVER_A\Advanced, I set the Connection Timeout to 1.
  • Linked Server Properties for SERVER_A\Advanced, I set the Query Timeout to 1.
  • Local server Properites\Advanced, I set Remote Login Timeout to 1.
    (Same as EXEC sp_configure 'remote login timeout', 1)
  • Local server Properites\Connections, I set Remote query timeout to 10.
    (Same as EXEC sp_configure 'remote query timeout', 10)
  • I looked through all of the settings in sys.configurations and couldn't find any that are anywhere near 21.

Regardless of what settings I change, the timeout still takes 21 seconds and returns the above error message.

What am I missing?


UPDATE:

I tried setting the linked server connection timeout to 43. After that, it took 63 seconds to timeout. We're getting somewhere! Setting it to 30 or 33 times out in 42 seconds, etc. Something is adding either 12 or 20 seconds to the login timeout.

After more testing, it appears that the timeouts I'm experiencing are in multiples of 21 seconds. It's taking 21, 42 or 63 seconds to timeout. It takes longer as I increase the Connection Timeout past a multiple of 21. e.g. if I set the Connection Timeout to 21, it takes 42. If I set it to 50, it takes 63.

3
  • Could you query sys.servers to check the values for connect_timeout and query_timeout? Also, did you run a RECONFIGURE after changing the options with the sp_configure?
    – Ronaldo
    Dec 10 '20 at 19:29
  • Thanks for the ideas, Ronaldo. When I query sys.servers, I see the configurations I entered: connect_timeout=1 and query_timeout=1, same as the settings I see in the linked server properties box. Besides those two, I don't see other columns that have an obvious connection to timeouts. Also, I did use RECONFIGURE after sp_configure. I meant to say that in the post.
    – Robbie
    Dec 10 '20 at 21:06
  • 1
    Did you try to run the remote query more than once? I had this problem with the first attempt taking a long time to fail as I described in my answer. Also, could you try configuring a high value (sp_configure 'remote login timeout', 0 ; and the linked server Connection Timeout 43) to see if it takes longer to throw the timeout error?
    – Ronaldo
    Dec 10 '20 at 21:31
1

I was able to achieve it in two ways:

Configuring a timeout for the whole server

You should configure the server timeout option (remote login timeout) using sp_configure:

EXEC sp_configure 'remote login timeout', 1 ;  
GO  
RECONFIGURE ;  
GO  

Configuring a timeout for the specified Linked Server

You should configure the Connection Timeout with the value 1 or more:

Linked Server properties

If you query sys.servers by now, you should see the specified value for the column connect_timeout.


I noticed a strange behavior after configuring it. SQL Server seems to try the remote connection longer than the specified connection timeout the first time you try to run a query using a linked server (as if it was insisting to be sure it can't reach the remote server). But the following attempts of running a query it threw the connection error quickly as specified.

8
  • I have my remote server configured exactly as yours, and have set 'remote login timeout' to 1 also, and tried the query twice. Still 21 seconds. HOWEVER, I have a new theory: There are two timeouts involved. Per your comment above, I tried setting the linked server connection timeout to 43. After that, it took 63 seconds to timeout. We're getting somewhere! Setting it to 30 or 33 times out in 42 seconds, etc. Something is adding either 12 or 20 seconds to the login timeout.
    – Robbie
    Dec 11 '20 at 16:49
  • As I noted in the original post, two error messages are generated. The first is "Login timeout expired" The second is "A network-related or instance-specific error has occurred..." I'm guessing that it's the second error that's taking the extra time. Do you know where the timeout is for the second one?
    – Robbie
    Dec 11 '20 at 16:51
  • @Robbie, I also got both error messages and I didn't have to configure any other place besides the two options in my answer. One thing that is not clear to me in your comment: are you configuring the timeout on the destine (remote) server instead of the origin server? Like: you have a linked server from server A to B and your timeout is configured on server B? Because the timeout should be on server A as server A should decide to stop trying to connect to server B after the configured timeout.
    – Ronaldo
    Dec 11 '20 at 19:39
  • I'm making all of these configurations changes on the source server, Server A, as you say, not the target. I should have made that clear. (I presume that configurations on the target server would be unavailable if that server is down.) After more testing, it appears that the timeouts I'm experiencing are in multiples of 21 seconds. It's taking 21, 42 or 63 seconds to timeout. It takes longer as I increase the Connection Timeout past a multiple of 21. e.g. if I set the Connection Timeout to 21, it takes 42. If I set it to 50, it takes 63. Very curious!
    – Robbie
    Dec 11 '20 at 20:21
  • 1
    unfortunately, no. I've had my attention elsewhere. If/when I do find a solution, I'll certainly post it here. Thanks again for all your help.
    – Robbie
    Dec 22 '20 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.