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:
![enter image description here]()
Next, I took a look at the Tasks window, which was of a much better help:
![enter image description here]()
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.
