How can I solve a connection pool problem between

Question

The last few days we see this error message in our website too much:

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

We have not changed anything in our code in a while. I revised the code to check open connections which didn't close, but found everything to be fine.

• How can I solve this?

• Do I need to edit this pool?

• How can I edit this pool's max number of connections?

• What is the recommended value for a high traffic website?

---

Update:

Do I need to edit something in IIS?

Update:

I found that the number of active connections are anywhere from 15 to 31, and I found that the max allowed number of connections configured in SQL server is more than 3200 connections, is 31 too many or should I edit something in the ASP.NET configration?

Answer 1

If you are working on complex legacy code where a simple using(..) {..} isn't possible - as I was - you may want to check out the code snippet I posted in this SO question for a way to determine the call stack of the connection creation when a connection is potentially leaked (not closed after a set timeout). This makes it fairly easy to spot the cause of the leaks.

Answer 2

In my case, I was not closing the DataReader object.

        using (SqlCommand dbCmd = new SqlCommand("*StoredProcedureName*"))
        using (dbCmd.Connection = new SqlConnection(WebConfigurationAccess.ConnectionString))
            {
            dbCmd.CommandType = CommandType.StoredProcedure;

            //Add parametres
            dbCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = ID;
.....
.....
            dbCmd.Connection.Open();
            var dr = dbCmd.ExecuteReader(); //created a Data reader here
            dr.Close();    //gotta close the data reader
            //dbCmd.Connection.Close(); //don't need this as 'using' statement should take care of this in its implicit dispose method.
            }

Answer 3

In most cases connection pooling problems are related to "connection leaks." Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.

You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open and Close throws an exception:

var connection = new SqlConnection(connectionString);
connection.Open();
// some code
connection.Close();                

The correct way would be this:

var connection = new SqlConnection(ConnectionString);
try
{
     connection.Open();
     someCall (connection);
}
finally
{
     connection.Close();                
}

or

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

When your function returns a connection from a class method make sure you cache it locally and call its Close method. You'll leak a connection using this code for example:

var command = new OleDbCommand(someUpdateQuery, getConnection());
result = command.ExecuteNonQuery();
connection().Close(); 

The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.

If you use SqlDataReader or a OleDbDataReader, close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.

---

This article "Why Does a Connection Pool Overflow?" from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:

• Run sp_who or sp_who2. These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
• Use SQL Server Profiler with the SQLProfiler TSQL_Replay template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.
• Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
• Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.

Answer 4

We encounter this problem from time to time on our web site as well. The culprit in our case, is our stats/indexes getting out of date. This causes a previously fast running query to (eventually) become slow and time out.

Try updating statistics and/or rebuilding the indexes on the tables affected by the query and see if that helps.

Answer 5

This problem i had in my code. I will paste some example code i have over came below error. 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.

 String query = "insert into STATION2(ID,CITY,STATE,LAT_N,LONG_W) values('" + a1 + "','" + b1 + "','" + c1 + "','" + d1 + "','" + f1 + "')";
    //,'" + d1 + "','" + f1 + "','" + g1 + "'

    SqlConnection con = new SqlConnection(mycon);
    con.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = query;
    cmd.Connection = con;
    cmd.ExecuteNonQuery();
    **con.Close();**

You want to close the connection each and every time. Before that i didn't us the close connect due to this i got error. After adding close statement i have over came this error

Answer 6

This problem I have encountered before. It ended up being an issue with the firewall. I just added a rule to the firewall. I had to open port 1433 so the SQL server can connect to the server.