Currently all connections getting "Timeout expired"

I have an application which connects to SQL Server. Currently I am getting,

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.

When I ran,

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
     DB_NAME(dbid) ='MyDb'
GROUP BY 
    dbid, loginame 

DBName  NumberOfConnections LoginName
MyDb    10                     sa                                                                                                                              
MyDb    109                   MyUser

The status of all process is sleeping and cms is AWAITING COMMAND

Here is my code,

```
private async Task ExecuteAsync(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel, SqlParameter[] parameters, Func callback = null)
{
var stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new SqlConnection(_settings.DatabaseConnectionString))
{
using (var command = new SqlCommand(commandText, connection) {CommandType = commandType})
{
command.Parameters.AddRange(parameters);
await connection.OpenAsync().ConfigureAwait(false);
command.CommandTimeout = _settings.CommandTimeout;
var transaction = connection.BeginTransaction(isolationLevel);
command.Transaction = transaction;
try
{
object result;
switch (executionType)
{
case ExecutionType.Reader:
var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
using (reader)
{
var list = new List();
while (reader.Read())

It looks like you have an application that isn't properly closing or disposing of the SqlConnection objects. By default, SqlConnection has a max pool size of 100.

The fix here would be to work with the application to find out why connections aren't being cleaned up, as they are still "active" in the particular connection pool, which is why you are unable to grab another one, because there are no inactive connections in the pool to use.

The application should be making a call to SqlConnection.Close() or SqlConnection.Dispose() in order to release the connection and mark it as "inactive".

I wrote an extensive blog post (Connection Pooling for the SQL Server DBA) about connection pooling, and it should shed some light on why you are seeing what you are seeing, and also the programmatic fix to the issue.

StackExchange Database Administrators Q#81372, answer score: 8