THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Linked servers: how long do they stay connected?

I was looking at some of my old notes on linked servers and found a tidbit on how the linked server connections are managed by SQL Server. I'm posting it here because I don’t think the information is widely known.

When you make a linked server call from a SQL Server instance (say ServerA) to another SQL Server instance (say ServerB) over Microsoft Native Client OLEDB Provider, SQL Server on ServerA acts as a client to the instance on ServerB and will open or reuse a connection to ServerB. That connection will be managed by the SQL Server instance on ServerA.

If you check on ServerB with the following query, you should see that connection from ServerA (if it's still there):

select * from sysprocesses

 where hostname = 'ServerA'

   and program_name = 'Microsoft SQL Server'

And if no linked server calls from ServerA is using the connection, its status would be ‘dormant’.

The question is, “how long will ServerA keep the connection alive if no call is using it? And can you configure it?”

I can’t find any official documentation to answer these two questions. But my own tests appear to yield a consistent answer to the first question. That is, a dormant SQL Server linked server connection will stay for about 4~5 minutes, and will be closed after that. All my attempts to see if this number is configurable suggest that the answer is negative. If anyone knows a more authoritative answer, please post it.

Here is a simple test to determine how long a dormant connection stays alive.

On ServerB, use the previous query to ensure that there is no linked server connection from ServerA. If there is, kill the connection and ensure ServerA does not open a new one.

On ServerB, run this script:

declare @dt datetime

while not exists (select * from sysprocesses

                   where hostname = 'ServerA'

                     and program_name = 'Microsoft SQL Server')

    waitfor delay '00:00:01'

 

set @dt = getdate()

 

while exists (select * from sysprocesses

               where hostname = 'ServerA'

                 and program_name = 'Microsoft SQL Server')

    waitfor delay '00:00:01'

 

select 'Duration' = datediff(second, @dt, getdate())

 

Go to ServerA and run the next script:

 

select * from openquery(ServerB, 'select @@servername')


I have been consistently getting the Duration values between ~260 seconds and ~300 seconds, which is about 4~5 minutes.
If you have any test numbers of your own  or run this test, please post your findings.

Published Thursday, March 31, 2011 9:36 PM by Linchi Shea
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Vincent Salard said:

Hi Linchi,

Running your test, I'm getting results between 248 & 259.

I'm curious...

April 1, 2011 3:50 AM
 

Dave said:

300 seconds seems to do with the standard duration provided with most drivers. Have you tried to play with these data driver specific settings?

April 4, 2011 11:39 AM
 

Anthony said:

This sounds like you have the default settings for remote connections.

if you run the below

sp_configure ('show advanced options',1)

go

reconfigure with override

go

sp_configure

go

you then want to look for remote query timeout should be around a value of 300, if you change this to a value in seconds and reconfigure

this should keep the connections alive for as long as you specify

May 25, 2011 9:22 AM
 

Adam said:

The command sp_configure 'remote query timeout (s)', <value>  only affect a query timeout, not a connection timeout.  

February 2, 2012 7:05 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement