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

In my case, I had infinite loop (from a get Property trying to get value from database) which kept opening hundreds of Sql connections.

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

Answer 2

Use this:

finally
{
    connection.Close();
    connection.Dispose();
    SqlConnection.ClearPool();
}

Answer 3

In addition to the posted solutions....

In dealing with 1000 pages of legacy code, each calling a common GetRS multiple times, here is another way to fix the issue:

In an existing common DLL, we added the CommandBehavior.CloseConnection option:

    static public IDataReader GetRS(String Sql)
    {
        SqlConnection dbconn = new SqlConnection(DB.GetDBConn());
        dbconn.Open();
        SqlCommand cmd = new SqlCommand(Sql, dbconn);
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);   
    }

Then in each page, as long as you close the data reader, the connection is also automatically closed so connection leaks are prevented.

    IDataReader rs = CommonDLL.GetRS("select * from table");
    while (rs.Read())
    {
        // do something
    }
    rs.Close();   // this also closes the connection

Answer 4

Did you check for DataReaders that are not closed and response.redirects before closing the connection or a datareader. Connections stay open when you dont close them before a redirect.

Answer 5

Don't instantiate the sql connection too much times. Open one or two connections and use them for all next sql operations.

Seems that even when Disposeing the connections the exception is thrown.

Answer 6

Upon installing .NET Framework v4.6.1 our connections to a remote database immediately started timing out due to this change.

To fix simply add the parameter TransparentNetworkIPResolution in the connection string and set it to false:

Server=myServerName;Database=myDataBase;Trusted_Connection=True;TransparentNetworkIPResolution=False