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

Ceasefire

"Timeout expired." - I can think of many reasons why a timeout would occur before assuming you've hit the max connections.... – Gnotobiotics

@MitchWheat - True, only the error message is clear about the max connections being hit. – Kirstiekirstin

What @DoanCuong is asking for is to see how you are opening and closing connections. If you are not closing them properly, you could easily cause connection pool starvation. – Kirstiekirstin

@Oded: True, but I do alot of work with .NET and databases and I've not seen that error yet.... – Gnotobiotics

@MitchWheat - Nor have I. The only reason I can think is that each and every connection is kept open once obtained. – Kirstiekirstin

is there any way to clear connection pool automatically like SqlConnection.ClearAllPools(). – Ceasefire

@Harshit - please post your code. We can't give you concrete advice without seeing what you are doing. – Kirstiekirstin

The only time I faced this error is when I tried to select data in the middle of a transaction – Lalise

Where are you completing the transaction? If you never complete it... – Kirstiekirstin

Problem is solved by increase max pool size in connection string Max Pool Size=100 – Ceasefire

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.

Tuscarora

Problem is solved by increase max pool size in connection string Max Pool Size=100 – Ceasefire

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.

Publican

Answer 3

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: http://msdn.microsoft.com/en-us/library/ms254507(v=vs.80).aspx, 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.

Pyxis

Answer 4

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 http://sqlserverplanet.com/troubleshooting/sql-server-slowness

Publican