Test Your Linked Server Connections

Question

Post reply

Test Your Linked Server Connections

mrpaulandrew

Mr or Mrs. 500

Points: 552
More actions
August 26, 2014 at 2:23 pm

#298860

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

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

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

andyscott Ten Centuries Points: 1264 More actions · Answer 1

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

Robert.Sterbal SSCrazy Points: 2855 More actions · Answer 2

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 More actions · Answer 3

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

Don Urquhart SSCrazy Points: 2423 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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

Don Urquhart SSCrazy Points: 2423 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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: 1195 More actions · Answer 14

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!