Test Your Linked Server Connections

  • mrpaulandrew

    Mr or Mrs. 500

    Points: 502

    Comments posted to this topic are about the item Test Your Linked Server Connections

  • andyscott

    Ten Centuries

    Points: 1154

    ....just love the way they are described as "desperate" databases!

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    This is very handy in my environment. Would you like me to message you for permission to post the script on my company's internal wiki?

    Thanks!

  • msspurlock

    SSC Journeyman

    Points: 78

    I can't believe no one restarted the old argument about whether 0 equals success. 🙂

  • Don Urquhart

    SSCrazy

    Points: 2423

    Does anyone know the proper way to shorten the login timeout when using the sp_testlinkedserver?

    Currently my timeouts seem to take between 20-27 seconds to timeout which is a problem with 100+ linked servers on our VPN.

    I started this question http://www.sqlservercentral.com/Forums/Topic1608372-1550-1.aspx but haven't received any replies and really need to find an answer.


    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    Don Urquhart (9/4/2014)


    Does anyone know the proper way to shorten the login timeout when using the sp_testlinkedserver?

    Currently my timeouts seem to take between 20-27 seconds to timeout which is a problem with 100+ linked servers on our VPN.

    I started this question http://www.sqlservercentral.com/Forums/Topic1608372-1550-1.aspx but haven't received any replies and really need to find an answer.

    Try this article to see if it fits your situation:

    http://sqlblogcasts.com/blogs/markallison/archive/2008/01/29/linked-server-timeouts.aspx

  • Don Urquhart

    SSCrazy

    Points: 2423

    Robert.Sterbal (9/4/2014)


    Try this article to see if it fits your situation:

    http://sqlblogcasts.com/blgs/markallison/archive/2008/01/29/linked-server-timeouts.aspx%5B/quote%5D

    I have changed the timeout values both for the local server and for the linked servers, but they seem to have had no effect.


    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    Did you set the time out for one at 500 seconds to see if it made it worse?

  • Don Urquhart

    SSCrazy

    Points: 2423

    Robert.Sterbal (9/4/2014)


    Did you set the time out for one at 500 seconds to see if it made it worse?

    Sorry for the slow reply; was away at comp. conference.

    I changed the Server Remote Login Timeout to 100 and then 600 sec.s - no effect. Though it seemed it took effect immediately, I rebooted the server just to be sure - no difference.

    The Linked Server Connection Timeout value does make a difference. Changing it from 5 sec.s to 100 changed the time I saw from around 20 seconds to around 2 minutes. However changing it to 1 sec. I'm still taking 20+ sec.s.

    I even tried the Linked server Query Timeout value - no difference.

    For servers that are there this test takes < 1 sec.

    It seems that it is the server I need to change but I don't know how to make it take effect.


    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    The workaround suggested by the most experienced person in my group was to use sqlcmd and parse the results.. You could name the output file by server name, then take a directory of the output files to see which servers are responding.

  • Don Urquhart

    SSCrazy

    Points: 2423

    Robert.Sterbal (9/8/2014)


    The workaround suggested by the most experienced person in my group was to use sqlcmd and parse the results.. You could name the output file by server name, then take a directory of the output files to see which servers are responding.

    Assuming that I'm using sqlcmd to run the same procedure to test I would expect it to take the same amount of time to error out which wouldn't fix my issue. Or are you suggesting something else?


    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    well, you can tell which links are working before you have to wait on the ones that aren't.

    You could also just use the links that replied within a certain amount of time.

  • Don Urquhart

    SSCrazy

    Points: 2423

    Robert.Sterbal (9/8/2014)


    well, you can tell which links are working before you have to wait on the ones that aren't.

    You could also just use the links that replied within a certain amount of time.

    Agreed, but unfortunately it still would leave me potentially in a bind for those links that aren't there, due to the nature of the application.

    Thanks, anyway. Always willing to consider alternatives.


    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    I guess I'm saying that instead of using the stored procedure, implement a process of testing the links by running SQLCMD and when you get a result, you will know that the link is up.

  • Kris Gruttemeyer

    Ten Centuries

    Points: 1185

    Great script. I've implemented it as a SQL agent job that insert into my DBA Support DB and alerts me via email if a server goes offline. We actually were able to catch an issue already with one of our linked servers because of this script. Thanks so much!

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply