sql-serverconnection-pooling

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.


Solution

  • A few years ago the answer beneath was the situation, but now it's changed so you can refer to the source and write up a summary :)


    Old answer

    This excellent article tells us what we need to know, using reflection to reveal the inner workings of connection pooling.

    From how I understand it, 'closed' connections are cleaned up periodically on a semi-random interval. The cleanup process runs somewhere between every 2min and 3min 50s, but it needs to run twice before a 'closed' connection will be properly closed. Therefore after 7min 40s of being 'closed' the underlying sql connection should be properly closed, but it could be as short as 2min. At the time of writing the first connection pool created in a process would always have a timer interval of 3min 10s, so you'd normally see sql connections being closed somewhere between 3min 10s and 6min 20s after you call Close() on the ADO object.

    Obviously this uses undocumented code so could change in future - or could even have changed since that article was written.