Clear Connection pool

The relationship between Connection pool and connection string

Connection pool and connection string go hand in hand. Every connection pool is associated with a distinct connection string and that too, it is specific to the application. In turn, what it means is – a separate connection pool is maintained for every distinct process, app domain and connection string.

 

A connection pool is also per process. So if you have two instances (or two web servers) of your application running and each have 50 connections in the pool, you will have a total of 100.

Connection Pool Deletion / Clearing Connection Pool (refer to http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx)

Connection pool is removed as soon as the associated app domain is unloaded. Once the app domain is unloaded, all the connections from the connection pool become invalid and are thus removed. Say for example, if you have an ASP.NET application, the connection pool gets created as soon as you hit the database for the very first time, and the connection pool is destroyed as soon as we do iisreset . We'll see it later with example. Note that connection pooling has to do with IIS Web Server and not with the Dev Environment, s o do not expect the connection pool to be cleared automatically by closing your Visual Studio .NET dev environment.

ADO.NET 2.0 introduces two new methods to clear the pool: ClearAllPools and ClearPool . ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections in use at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.

Refer to the section "Simple Ways to View Connections in the Pool Created by ADO.NET" for details on how to determine the status of the pool.

 

 

Clearing Connection Pool  (refer to http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx)

Solution(s):

   1. If you are working with .NET and Oracle using ODP.NET v 9.2.0.4 or above, you can probably try adding "Validate Connection=true" in the connection string. Well, in couple of places, I noticed people saying use "validcon=true" works for them for prior versions on ODP.NET. See which works for you. With ODP.NET v 9.2.0.4, "validcon=true" errors out and "Validate Connection=true" works just fine.
   2. If you are working with .NET 2.0 and Microsoft SQL Server , You can clear a specific connection pool by using the static (shared in Visual Basic .NET) method SqlConnection.ClearPool or clear all of the connection pools in an appdomain by using the SqlConnection.ClearPools method. Both SqlClient and OracleClient implement this functionality.
   3. If you are working with .NET 1.1 and Microsoft SQL Server :
         1. In the connection string, at run time, append a blank space and try establishing the connection again . What in turn it would do is, a new connection pool would be created and will be used by your application, In the meantime the prior pool will get removed if it's not getting used.
         2. Do exception handling, and as soon as you get this error try connection afresh repeatedly in the loop. With time, ADO.NET and database server will automatically get in sync.
            Well, I am not totally convinced with either approach, but frankly speaking, I could not get any better workable solution for this so far.

 

 

ClearAllPools resets (or empties) the connection pool. (refer to http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.clearallpools(VS.80).aspx)

If there are connections in use at the time of the call,

they are marked appropriately and will be discarded (instead of being returned to the pool) when Close is called on them.