I'm using the following code to check if a linked server is available before running queries on it:
declare @srvr nvarchar(128), @retval int; set @srvr = 'my_linked_srvr'; begin try exec @retval = sys.sp_testlinkedserver @srvr; end try begin catch set @retval = sign(@@error); end catch; if @retval <> 0 raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
I've set the 'Connection Timeout' and 'Query Timeout' to 3 seconds on the linked server settings. If I stop the SQL Server that is linked, the script takes 3 seconds to time out and report the failure.
If I pull the network cables or disable the network adapters, it takes 18 seconds to timeout.
Why is it not adhering to the timeout settings and can I speed this up? 18 seconds to each time to test 3 linked servers is a little too slow.