No connections available in ADO.NET connections pool

I'm trying to figure out the cause of the increasing number of exceptions on a production environment related to the ADO.NET connections pool, I can see in the log a lot of exceptions of this kind:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

The first thing that I tried was to be sure that all connections are been closed and disposed. To do that we changed all database connections to:

try
{
     oConnection.Open();
     //do something
}    
catch()
{

}
finally
{
     if (oConnection.State != ConnectionState.Closed)
     {
         oConnection.Close();
         oConnection.Dispose();
     }
}

I'm using the following connection string:

开发者_如何学Goserver=databaseserver;Database=databasename;User Id=username;Password=password;Max Pool Size=600;Min Pool Size=50;Connection Timeout=180;Pooling=true

In Performance Manager, .NET Data Provider for SqlServer, I can see that the "NumberOfPooledConnections" and the "NumberOfActivePooledConnections" are always below the Max Pool Size of the connection string, always between 60 and 90 connections.

Does anyone knows how can it be possible? I'm getting the exception that there's no connections on the pool, but I can see that the pool isn't full.

My environment:

• Windows Server 2003 Enterprise SP2 (with all recent patchs)
• IIS 6.0 (with 4 worker processes in web garden and App Pool recycle every 60 minutes)
• SQL Server 2005

May anyone help me?

using (oConnection) {

   oConnection.Open();

   // Your code here

   // No need to explicitly close or dispose

}

This piece of code

if (oConnection.State != ConnectionState.Closed)
{
     oConnection.Close();
     oConnection.Dispose();
}

will not Dispose() a Closed connection. I'm not sure that that is the problem but it should be fixed. Jusr use an nconditional Dispose() or a using() { } block.

The state might be closed but the connection is still not disposed so you are not really disposing the connection then, use the using block as suggested before.

Firstly, the setting for max connections allowed by SQL Server is different than the ADO.NET setting for max allowed connections in .NET connection pool.

Secondly, calling SqlConnection.Close() does not actually close its connection to SQL Server. It simply tells the connection pool that there's no need to keep the connection open any longer. However, the connection pool will keep it open for a bit longer in case another connection request with the same connection string is requested. To observe this, check out SQL Server's Activity Monitor before and after you "close" the connection. You'll see that the connection's state on SQL Server is actually sleeping.

As for your timeout exceptions, I agree with everyone's suggestion to use the using() { } block. On top of that, if you're using a SqlDataReader, make sure you're closing that as well.