how long must a sql server connection be idle before it is closed by the connection pool?

views:

1362

answers:

+1 Q:

how long must a sql server connection be idle before it is closed by the connection pool?

I have a client-server app that uses .NET SqlClient Data Provider to connect to sql server - pretty standard stuff. By default how long must connections be idle before the connection pooling manager will close the database connection and remove it from the pool? What setting if any controls this?

This MSDN document only says

The connection pooler removes a connection from the pool after it has been idle for a long time, or if the pooler detects that the connection with the server has been severed.

+1 A:

Please go through this:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28VS.80%29.aspx

The part

"The following table lists the valid names for connection pooling values within the ConnectionString."

seems to be of your interest.

Ganesh R. 2009-07-16 11:17:21

hmmm... the only setting that might be appropriate there is Load Balance Timeout, but that's a minimum not a max time so increasing it should make connections last longer. I want to know what "idle for a long time" means by default. If it's the connection pooling that decides a connection has been idle then it shouldn't be dependent on the SQL Server connection timeout. Also is there such a thing as the connection timeout of the SQL Server?

Rory 2009-07-16 11:44:04

One can use Connection Lifetime to decide the max life of a connection (idle + used time).

Ganesh R. 2009-07-16 12:02:19

Ganesh R. 2009-07-16 12:10:57

Connection Lifetime says it is only checked when a connection returns to a pool, and it's based on when the connection was created rather than how long since its last batch. Therefore I don't think it is useful in this context.

Rory 2009-07-16 12:45:37

A connection is removed from a connection pool and the connection to thedatabase server is terminated when it sits idle (unused) for too long. Thelength of time that a connection can sit idle in a connection pool before beingremoved is determined by the Connection Lifetime connection string option.A timestamp is attached to each connection that is released back to aconnection pool when the application calls Connection.Close(). The poolmanager then periodically checks the pools for connections that haveexceeded their Connection Lifetime.

Ganesh R. 2009-07-16 16:03:19

Note, however, that the data provider will always retain the number of connections specified by the Min Pool Size connection option in a connection pool. So, setting the Min Pool Size connection option greater than 0 means that many connections in a pool will effectively ignore the Connection Lifetime connection option. ---- This is what I found in a PDF on Connection Pooling by DATADIRECT TECHNOLOGIES

Ganesh R. 2009-07-16 16:05:01

According to the docs, ConnectionLifetime is the period since the connection was opened, not since it was last used. If so then it's not setting the idle timeout, and setting this will result in connections being unnecessarily closed when they're regularly being used. eg I want connections closed after 1 min of no use. If I use ConnectionTimeout then every db connection will be closed after 1 min. I only want ones that aren't being regularly used to be closed.

Rory 2009-07-16 17:43:37

ansaurus

tags:

views:

answers:

how long must a sql server connection be idle before it is closed by the connection pool?

related questions