I have code similar to this running in multiple BackgroundServices [.NET 7]. After just a few days of runtime (obviously, delay is in minutes (to hours) between loops) leads to a massive Memory Leak in tens of thousands of dangling SqlConnection handles (probably all of them ever used are still referenced, even if properly disconnected from the DB).

MRE

using System;
using System.Threading;
using System.Threading.Tasks;
using System.Diagnostics;

using Microsoft.Data.SqlClient;

namespace Memleak;

static class Program
{
    const string connectionString = "Server=lpc:localhost;"
          "Integrated Security=True;Encrypt=False;"
          "MultipleActiveResultSets=False;Pooling=False;";

    static async Task Main(params string[] args)
    {
        using CancellationTokenSource cts = new();
        // not to forget it running
        cts.CancelAfter(TimeSpan.FromMinutes(15));
        CancellationToken ct = cts.Token;

        using Process process = Process.GetCurrentProcess();
        long loop = 1;

        while (true)
        {
            await ConnectionAsync(ct);

            // this seems to be the issue (delay duration is irrelevant)
            await Task.Delay(TimeSpan.FromMilliseconds(1), ct);

            process.Refresh();
            long workingSet = process.WorkingSet64;
            Console.WriteLine("PID:{0} RUN:{1:N0} RAM:{2:N0}",
                process.Id, loop, workingSet);

              loop;
        }
    }

    private static async Task ConnectionAsync(CancellationToken ct = default)
    {
        using SqlConnection connection = new(connectionString);
        await connection.OpenAsync(ct);

        using SqlCommand command = connection.CreateCommand();
        command.CommandText = "select cast(1 as bit);";

        using SqlDataReader reader = await command.ExecuteReaderAsync(ct);
        if (await reader.ReadAsync(ct))
        {
            _ = reader.GetBoolean(0);
        }
    }
}

Leak

These following command prompt commands show the leak:

// dotnet tool install --global dotnet-dump
dotnet-dump collect -p pid
dotnet-dump analyze dump_name.dmp
dumpheap -type Microsoft.Data.SqlClient.SqlConnection -stat
dumpheap -mt mtid
dumpobj objid
gcroot objid

Last command shows a huge list of System.Threading.CancellationTokenSource CallbackNode for a SqlConnection object.

Question

Is this a bug or working as expected (and if so why)? And is there any easy workaround except getting rid of all async code and just using Threads? I cannot use Timers since Delays are variable upon certain factors (when work is available, delays are shorter; when work is not, delays are longer).

[update] Non-async version does not leak

using System;
using System.Threading;
using System.Threading.Tasks;
using System.Diagnostics;

using Microsoft.Data.SqlClient;

namespace NotMemleak;

static class Program
{
    const string connectionString = "Server=lpc:localhost;"  
        "Integrated Security=True;Encrypt=False;"  
        "MultipleActiveResultSets=False;Pooling=False;";

    static void Main(params string[] args)
    {
        using CancellationTokenSource cts = new();
        // not to forget it running
        cts.CancelAfter(TimeSpan.FromMinutes(15));
        CancellationToken ct = cts.Token;

        using Process process = Process.GetCurrentProcess();

        long loop = 1;
        while (loop < 1000)
        {
            Connection();

            // this seems to be the issue (delay duration is irrelevant)
            ct.WaitHandle.WaitOne(TimeSpan.FromMilliseconds(1));
            // Thread.Sleep();

            process.Refresh();
            long workingSet = process.WorkingSet64;
            Console.WriteLine("PID:{0} RUN:{1:N0} RAM:{2:N0}"
                , process.Id, loop, workingSet);

              loop;
        }

        Console.WriteLine();
        Console.WriteLine("(press any key to exit)");
        Console.ReadKey(true);
    }

    private static void Connection()
    {
        using SqlConnection connection = new(connectionString);
        connection.Open();

        using SqlCommand command = connection.CreateCommand();
        command.CommandText = "select cast(1 as bit);";

        using SqlDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
            _ = reader.GetBoolean(0);
        }
    }
}

CodePudding user response：

I believe here is related issue in github. As I understood it - it's a regression bug introduced in SqlClient 5.0.1. Basically, on this line:

await reader.ReadAsync(ct)

You pass token, and reader will internally register a callback function when this token is cancelled. However, this registration is not properly unregistered on all code paths. This in turn results in your SqlConnection instances be reachable from CancellationTokenSource through that callback registration (which references data reader, which references command, which references connection).

This is fixed in one of the preview releases, so you can try installing 5.1.0-preview2 and see if the issue is gone. There is no "production" release with this fix yet.