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.)
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 .
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.
EXEC sp_configure 'remote login timeout', 1)
- Local server Properites\Connections, I set Remote query timeout to 10.
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?
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.