Linked servers: how long do they stay connected?

Linchi Shea — Fri, 01 Apr 2011 00:36:00 GMT

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.

Search results matching tags 'Testing' and 'Linked server'

Linked servers: how long do they stay connected?