How to Solve Max Connection Pool Error

Question

I have a application in asp.net 3.5 and Database is Sql server 2005.

"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." 

Some time this error occured How to solve this error..

I try SqlConnection.ClearAllPools(); but this also not working.

SqlCommand cmdToExecute = new SqlCommand();             cmdToExecute.CommandText = "dbo.[sp_user_listing]";             cmdToExecute.CommandType = CommandType.StoredProcedure;             DataTable toReturn = new DataTable("courier_user_listing");             SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);              // Use base class' connection object             cmdToExecute.Connection = sqMainConnection;              try             {                 cmdToExecute.Parameters.Add(new SqlParameter("@suser_name", SqlDbType.VarChar, 250, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _user_name));                   if (blnMainConnectionIsCreatedLocal)                 {                     // Open connection.                     sqMainConnection.Open();                 }                 else                 {                     if (CPMainConnectionProvider.IsTransactionPending)                     {                         cmdToExecute.Transaction = CPMainConnectionProvider.CurrentTransaction;                     }                 }                  // Execute query.                 adapter.Fill(toReturn);                 i32ErrorCode = (Int32)cmdToExecute.Parameters["@iErrorCode"].Value;                  if (i32ErrorCode != (int)LLBLError.AllOk)                 {                     // Throw error.                     throw new Exception("Stored Procedure 'sp_courier_user_SelectAll' reported the ErrorCode: " + i32ErrorCode);                 }                  return toReturn;             }             catch (Exception ex)             {                 // some error occured. Bubble it to caller and encapsulate Exception object                 throw new Exception("Courier_user::SelectAll::Error occured.", ex);             }             finally             {                 if (blnMainConnectionIsCreatedLocal)                 {                     // Close connection.                     sqMainConnection.Close();                 }                 cmdToExecute.Dispose();                 adapter.Dispose();             } 

Answer 1

Check against any long running queries in your database.

Increasing your pool size will only make your webapp live a little longer (and probably get a lot slower)

You can use sql server profiler and filter on duration / reads to see which querys need optimization.

I also see you're probably keeping a global connection?

blnMainConnectionIsCreatedLocal 

Let .net do the pooling for you and open / close your connection with a using statement.

Suggestions:

1. Always open and close a connection like this, so .net can manage your connections and you won't run out of connections:

       using (SqlConnection conn = new SqlConnection(connectionString))     {      conn.Open();      // do some stuff     } //conn disposed 

2. As I mentioned, check your query with sql server profiler and see if you can optimize it. Having a slow query with many requests in a web app can give these timeouts too.

Answer 2

Here's what u can also try....

run your application....while it is still running launch your command prompt

while your application is running type netstat -n on the command prompt. You should see a list of TCP/IP connections. Check if your list is not very long. Ideally you should have less than 5 connections in the list. Check the status of the connections.
If you have too many connections with a TIME_WAIT status it means the connection has been closed and is waiting for the OS to release the resources. If you are running on Windows, the default ephemeral port rang is between 1024 and 5000 and the default time it takes Windows to release the resource from TIME_WAIT status is 4 minutes. So if your application used more then 3976 connections in less then 4 minutes, you will get the exception you got.

Suggestions to fix it:

1. Add a connection pool to your connection string.

If you continue to receive the same error message (which is highly unlikely) you can then try the following: (Please don't do it if you are not familiar with the Windows registry)

1. Run regedit from your run command. In the registry editor look for this registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters:

Modify the settings so they read:

MaxUserPort = dword:00004e20 (10,000 decimal) TcpTimedWaitDelay = dword:0000001e (30 decimal)

This will increase the number of ports to 10,000 and reduce the time to release freed tcp/ip connections.

Only use suggestion 2 if 1 fails.

Thank you.