How to Solve Max Connection Pool Error

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();
}

May be this is alltime multiple connection open issue, you are somewhere in your code opening connections and not closing them properly. use

using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
}

Refer this article:

[To see links please register here]

, **The Using block in Visual Basic or C# automatically disposes of the connection when the code exits the block, even in the case of an unhandled exception.**

Before you begin to curse your application you need to check this:

1. Is your application the only one using that instance of SQL Server.
a. If the answer to that is NO then you need to investigate how the other applications are consuming resources on your SQl Server.run
b. If the answer is yes then you must investigate your application.

2. Run SQL Server Profiler and check what activity is happening in other applications (1a) using SQL Server and check your application as well (1b).

3. If indeed your application is starved off of resources then you need to make farther investigations. For more read on this

[To see links please register here]

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)**

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

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.