[.net] How can I solve a connection pool problem between ASP.NET and SQL Server?

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?

I also got this exact error log on my AWS EC2 instance.

There were no connection leaks since I was just deploying the alpha application (no real users), and I confirmed with Activity Monitor and sp_who that there are in fact no connections to the database.

My issue was AWS related - more specifically, with the Security Groups. See, only certain security groups had access to the RDS server where I hosted the database. I added an ingress rule with authorize-security-group-ingress command to allow access to the correct EC2 instance to the RDS server by using --source-group-name parameter. The ingress rule was added, I could see that on the AWS UI - but I got this error.

When I removed and then added the ingress rule manually on AWS UI - suddenly the exception was no more and the app was working.

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.

I wasn't thinking this was my issue at first but in running through this list I discovered that it didn't cover what my issues was.

My issue was that I had a bug in which it tried to write the same record numerous times using entity framework. It shouldn't have been doing this; it was my bug. Take a look at the data you are writing. My thoughts are that SQL was busy writing a record, possibly locking and creating the timeout. After I fixed the area of code that was attempting to write the record multiple in sequential attempts, the error went away.

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.

This is mainly due to the connection not been closed in the application. Use "MinPoolSize" and "MaxPoolSize" in the connection string.

You can try that too, for solve timeout problem:

If you didn't add httpRuntime to your webconfig, add that in <system.web> tag

<sytem.web>
     <httpRuntime maxRequestLength="20000" executionTimeout="999999"/>
</system.web>

and

Modify your connection string like this;

 <add name="connstring" connectionString="Data Source=DSourceName;Initial Catalog=DBName;Integrated Security=True;Max Pool Size=50000;Pooling=True;" providerName="System.Data.SqlClient" />

At last use

    try
    {...} 
    catch
    {...} 
    finaly
    {
     connection.close();
    }

I have encountered this problem too, when using some 3rd party data layer in one of my .NET applications. The problem was that the layer did not close the connections properly.

We threw out the layer and created one ourselves, which always closes and disposes the connections. Since then we don't get the error anymore.

You can specify minimum and maximum pool size by specifying MinPoolSize=xyz and/or MaxPoolSize=xyz in the connection string. This cause of the problem could be a different thing however.

I was facing the same problem, after hours of research I realized I was connected on Guest network without VPN so setting up VPN did the trick for me

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.

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.

Make sure you set up the correct settings for connection pool. This is very important as I have explained in the following article: https://medium.com/@dewanwaqas/configurations-that-significantly-improves-your-app-performance-built-using-sql-server-and-net-ed044e53b60 You will see a drastic improvement in your application's performance if you follow it.

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

Yes, There is a way to change configuration. If you are on a dedicated server and simply need more SQL connections, you may update the "max pool size" entries in both connection strings by following these instructions:

1. Log into your server using Remote Desktop
2. Open My Computer (Windows - E) and go to C:\inetpub\vhosts[domain]\httpdocs
3. Double click on the web.config file. This may just be listed as web if the file structure is set to hide extensions. This will open up Visual Basic or similar editor.

4. Find your Connection Strings, these will look similar to the examples below :

   "add name="SiteSqlServer" connectionString="server=(local);database=dbname;uid=dbuser;pwd=dbpassword;pooling=true;connection lifetime=120;max pool size=25;""

5.Change the max pool size=X value to the required pool size.

6. Save and close your web.config file.

You have leaked connections on your code. You may try to use using to certify that you're closing them.

using (SqlConnection sqlconnection1 = new SqlConnection(“Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5”)) 
{
    sqlconnection1.Open();
    SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
    sqlcommand1.CommandText = “raiserror (‘This is a fake exception’, 17,1)”;
    sqlcommand1.ExecuteNonQuery();  //this throws a SqlException every time it is called.
    sqlconnection1.Close(); //Still never gets called.
} // Here sqlconnection1.Dispose is _guaranteed_

https://blogs.msdn.microsoft.com/angelsb/2004/08/25/connection-pooling-and-the-timeout-expired-exception-faq/

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

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

Yet another reason happened in my case, because of using async/await, resulting in the same error message:

System.InvalidOperationException: '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.'

Just a quick overview of what happened (and how I resolved it), hopefully this will help others in the future:

Finding the cause

This all happened in an ASP.NET Core 3.1 web project with Dapper and SQL Server, but I do think it is independent of that very kind of project.

First, I have a central function to get me SQL connections:

internal async Task<DbConnection> GetConnection()
{
    var r = new SqlConnection(GetConnectionString());
    await r.OpenAsync().ConfigureAwait(false);
    return r;
}

I'm using this function in dozens of methods like e.g. this one:

public async Task<List<EmployeeDbModel>> GetAll()
{
    await using var conn = await GetConnection();
    var sql = @"SELECT * FROM Employee";

    var result = await conn.QueryAsync<EmployeeDbModel>(sql);
    return result.ToList();
}

As you can see, I'm using the new using statement without the curly braces ({, }), so disposal of the connection is done at the end of the function.

Still, I got the error about no more connections in the pool being available.

I started debugging my application and let it halt upon the exception happening. When it halted, I first did a look at the Call Stack window, but this only showed some location inside System.Data.SqlClient, and was no real help to me:

Next, I took a look at the Tasks window, which was of a much better help:

There were literally thousands of calls to my own GetConnection method in an "Awaiting" or "Scheduled" state.

When double-clicking such a line in the Tasks window, it showed me the related location in my code via the Call Stack window.

This helped my to find out the real reason of this behaviour. It was in the below code (just for completeness):

[Route(nameof(LoadEmployees))]
public async Task<IActionResult> LoadEmployees(
    DataSourceLoadOptions loadOption)
{
    var data = await CentralDbRepository.EmployeeRepository.GetAll();

    var list =
        data.Select(async d =>
            {
                var values = await CentralDbRepository.EmployeeRepository.GetAllValuesForEmployee(d);
                return await d.ConvertToListItemViewModel(
                    values,
                    Config,
                    CentralDbRepository);
            })
            .ToListAsync();
    return Json(DataSourceLoader.Load(await list, loadOption));
}

In the above controller action, I first did a call to EmployeeRepository.GetAll() to get a list of models from the database table "Employee".

Then, for each of the returned models (i.e. for each row of the result set), I did again do a database call to EmployeeRepository.GetAllValuesForEmployee(d).

While this is very bad in terms of performance anyway, in an async context it behaves in a way, that it is eating up connection pool connections without releasing them appropriately.

Solution

I resolved it by removing the SQL query in the inner loop of the outer SQL query.

This should be done by either completely omitting it, or if required, move it to one/multilpe JOINs in the outer SQL query to get all data from the database in one single SQL query.

tl;dr / lessons learned

Don't do lots of SQL queries in a short amount of time, especially when using async/await.

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

I just had the same problem and wanted to share what helped me find the source: Add the Application name to your connection string and then monitor the open connections to the SQL Server

select st.text,
    es.*, 
    ec.*
from sys.dm_exec_sessions as es
    inner join sys.dm_exec_connections as ec on es.session_id = ec.session_id
    cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
where es.program_name = '<your app name here>'

Use this:

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

Unless your usage went up a lot, it seems unlikely that there is just a backlog of work. IMO, the most likely option is that something is using connections and not releasing them promptly. Are you sure you are using using in all cases? Or (through whatever mechanism) releasing the connections?

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

To reproduce the problem try this:

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

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.