Azure App Service connecting to SQL Azure gets all connections in pool used intermittently

Question

I have an application built in .net 6 that runs in Azure as an App Service. It connects to a SQL Azure database to run queries, update data etc.

Mostly this runs fine, but occasionally we run into a problem where lots of errors are generated in the app due to the connection pool running out of connections. Specifically the error is:

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.

The problem normally happens for a few minutes and then it resolves itself and everything goes back to normal.

So far I've:

Checked that the code isn't opening more db connections that necessary.
Checked that all connections are being closed
Checked performance on the Azure SQL server - it doesn't seem to indicate any spikes in usage at the times this is occurring
Increased the number of connections in the pool through the connection string. This has now been increased to 250, but the problem still keeps occurring

Troubleshooting is challenging as the problem is intermittent and we only discover it when we check App Insites for the site. The problem is occurring once every day or two - so not frequently, but still a problem.

How can I get more information on what might be causing this and how I can resolve it?

Example code:

protected DbConnection GetOpenConnection()
{
    var connection = new SqlConnection(configurationProvider.ConnectionString);
    connection.Open();

    return connection;
}

public int? GetUncommitedFilesCount(string machineName)
{
    using (var connection = GetOpenConnection())
    {
        var command = connection.CreateCommand();
        command.CommandText = @"Select UncommittedFilesCount from Machine Where MachineName = @MachineName";
        
        var parameter = command.CreateParameter();
        parameter.ParameterName ="@MachineName";
        parameter.Value = machineName;
        command.Parameters.Add(parameter);
        
        var result = command.ExecuteScalar();

        if (result == null)
        {
            return null;
        }

        return Convert.ToInt32(result);
    }
}

The issue looks like code issue. So [could you refer the link and share the code about the db connection](https://stackoverflow.com/a/670842/7687666), we need to check whether have connection leaks in your application. — Jason Pan, Jun 06 '23 at 02:39
All of the SQL connections are wrapped inside using clauses. — Matthew van Boheemen, Jun 06 '23 at 04:33
Could you share your sample code, removing sensitive information and business code. — Jason Pan, Jun 06 '23 at 05:06
You have all your usages of `SqlConnection` in using blocks. What about all usages of `SqlCommand` and `SqlDataReader`? They're all `IDisposable`. — AlwaysLearning, Jun 06 '23 at 09:11
Have you tried setting up Performance Monitors for whichever of [`System.Data.SqlClient`](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/performance-counters) or [`Microsoft.Data.SqlClient`](https://learn.microsoft.com/en-us/sql/connect/ado-net/performance-counters) you're using? Your own program can record and log metrics about its connection usages. — AlwaysLearning, Jun 06 '23 at 09:15
All usages of SqlDataReader are in using blocks. SqlCommands are not in using blocks, my understanding is that while they are IDisposable they don't need to be disposed to free the connection (as that is the SqlConnection object). Is this understanding incorrect? — Matthew van Boheemen, Jun 07 '23 at 04:04

score 0 · Answer 1 · answered Jun 09 '23 at 19:41

Try this:

public int? GetUncommitedFilesCount(string machineName)
{
    using (var connection =  new SqlConnection(configurationProvider.ConnectionString))
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = @"Select UncommittedFilesCount from Machine Where MachineName = @MachineName";
        
        var parameter = command.CreateParameter();
        parameter.ParameterName ="@MachineName";
        parameter.Value = machineName;
        command.Parameters.Add(parameter);
        
        var result = command.ExecuteScalar();

        connection.Close();
        if (result == null)
        {
            return null;
        }

        return Convert.ToInt32(result);
    }
}

Azure App Service connecting to SQL Azure gets all connections in pool used intermittently

1 Answers1