Take the 2-minute tour ×
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.

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
    
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. –  Michael 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
add comment

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

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

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.