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.

migrated from stackoverflow.com Mar 23 '13 at 7:27

This question came from our site for professional and enthusiast programmers.

  • I think you're experiencing a timeout that exists on the network adapter. Take note to the fact that you're not really comparing apples and oranges since you're literally changing the environment and now dealing with the fail overs that exist for the hardware. – Mike Perrenoud Mar 20 '13 at 16:58
  • I think you're correct - if I simply stop the SQL Server service, it seems to honour the timeout I set. If I physically unplug the machine, it takes an absolute age to time out. – Tim Mar 20 '13 at 17:06

Your Answer

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