Sql-server – Query to Linked Server Ignores Configured Timeout Connection

connectivitylinked-serversql serversql-server-2017timeout

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.

Best Answer

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.

Related Question