Connection pool close timeout

[Kevin Jackson]

It appears that with ADO.NET, an entry is removed from the connection pool
after 5 minutes of being not used. At least this is how it appears as we
watch the SPIDs on SQL Server.

This used to be 1 minute in ADO.

Can anyone confirm this 5 minute value?

 

[William Ryan eMVP]

By Default I believe the connection lifetime is set to 0 inidicating that
it's unlimited AFAIK.

 

[Kevin Yu [MSFT]]

Thanks for William's quick response.

Hi Kevin,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to confirm the 5 minutes'
connection lifetime in ADO.NET. If there is any misunderstanding, please
feel free to let me know.

As far as I know, the default value for the connection lifetime is '0'.
When a connection is returned to the pool, its creation time is compared
with the current time, and the connection is destroyed if that time span
(in seconds) exceeds the value specified by Connection Lifetime. This is
useful in clustered configurations to force load balancing between a
running server and a server just brought online. A value of zero (0) causes
pooled connections to have the maximum connection timeout. So please check
your connection string and see if the value of Connection Lifetime has been
set to 5 minutes.

Here is a link with more information about Connection Lifetime property in
connection string for your reference:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqlconnectionclassconnectionstringtopic.asp

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[William \(Bill\) Vaughn]

I'm Bill and I'll be really answering your question.
No, setting the ConnectionTimeout property has no affect on normal
(unclustered) connection pools. When the connection is dormant for 4-8
minutes (a random time between those ranges) it's "real" connection is
closed and the connection is purged from the pool. It's a common mistake to
think the ConnectionTimeout actually has an affect on this timeout.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Kevin Jackson]

Bill,

Thanks for confirming what we are really seeing. This is exactly what we
are seeing in our testing. Anywhere from 4-8 minutes, connections in the
pool that haven't been used are physically closed (SPIDs closed on the
database) and removed from the pool.

This behavior is documented NOWHERE! ConnectionLifetime is documented but
we have that set to the default of zero which would lead one to believe your
connections NEVER timeout and get closed. However, we see this is not true.

Thanks for the confirmation.

Kevin

 

[William \(Bill\) Vaughn]

Ah no. It's documented in my book and in an article I wrote on connection
handling. See http://www.betav.com/sql_server_magazine.htm. But you're
right, the help files don't really say this very clearly.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Angel Saenz-Badillos[MS]]

The help files only confuse the issue unfortunately, here is a few comments
on the subject:

We are changing the name of "Connection Lifetime" to "load balance timeout"
which is what it really does. Bottom line if you are not using server
clusters do not use this property. Of course we will maintain compatibility
via an alias to connection lifetime but at least the docs will not be so
confusing.

"> This behavior is documented NOWHERE!"
This is internal behavior of the pooler and subject to change at any time as
we fine tune for performance, we cannot possibly document it. This behavior
has been noted in non-Microsoft documentation like Bills excellent book.

For a complete "unofficial" description on how the managed pooler works you
can take a look at the following article, it even includes the code for the
hack.
Be aware that any time you are using reflection to touch non public objects
you risk having issues when a new version of the framework comes along, this
is also clearly stated in the article.
http://www.sys-con.com/dotnet/article.cfm?id=483