Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from 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 posting your answer, you agree to the privacy policy and terms of service.