Linked Servers not timing out

Question

Linked Servers not timing out

Ry-SQL

SSChasing Mays

Points: 601
More actions
January 26, 2012 at 2:04 pm

#249736

hi -
i have a native linked server on my sql 2008 r2 which points to another sql 2008 r2. with any combination of setting the linked server query timeout from 0 to 5 and connection timeout from 0 to 5 and changing the sp_configure remote query timeout from 0 to 5 i can not get my query to timeout.
what are the needed settings to get a native linked server query to timeout?
thanks all

Viewing 8 posts - 1 through 7 (of 7 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I have a question first. Has the connection EVER worked between them? Not just timing out but connections in general or ones that change some data?

My first though would be that the MS DTC is not configured to allow remote transactions. I have seen those cases NEVER timeout regardless of the other settings.

CEWII

Ry-SQL SSChasing Mays Points: 601 More actions · Answer 2

yes, i havent had any issues querying other servers. you think its a ms dtc setting?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Yes, I ran into this kind of problem before. On the remote end check to make sure that Network DTC Access is allowed, I'd probably look at another server of similar OS version that works to mimic..

CEWII

Ry-SQL SSChasing Mays Points: 601 More actions · Answer 4

no dice..network dtc access is on, allow inbound / outbound is on w/ mutual authentication..enable XA is on and the DTC logon uses network service..

the os's are similar in that they are virtual, just the cpu and memory are the main differences

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Elliott Whitlow

SSC Guru

Points: 102402

January 26, 2012 at 3:29 pm

#1439075

Firewall is the next place to look..

CEWII

Ry-SQL SSChasing Mays Points: 601 More actions · Answer 6

just confirmed with the network team that the local windows firewalls are not on and that there are no firewalls turned on between the 2 servers working together over the LAN.

interesting article from Ken Henderson about 'no such thing as query timeouts'

http://blogs.msdn.com/b/khen1234/archive/2005/10/20/483015.aspx

what im trying to achieve is to have a process cut off after X minutes. it just so happens all of these processes run via linked servers.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

As long as rows are being returned or it reports it is doing something it will not time-out. What you are asking for is not a typical time-out. I can think of a couple ways to do this but I'm not sure you should be doing it.

CEWII