Skip to content

Unexpected data lengths when asynchronously reading Stream using SequentialAccess behaviourΒ #3572

@edwardneal

Description

@edwardneal

Describe the bug

Trying to read a large varbinary(max) column asynchronously doesn't always return the full contents of the column. It doesn't throw any exceptions.

This only occurs with a specific combination of properties:

  • Low packet size (I've tested with 512, 768, 900, 1024 and 1500 bytes)
  • SqlDataReader opened with a behaviour of SequentialAccess
  • Reading a varbinary(max) using a Stream
  • Async pathways being used from end to end

If I raise the packet size, use a SqlDataReader with a behaviour of Default, read the field as a byte[] or use the sync pathways from end to end, this works.

My reproduction runs this SQL statement:

select top 512 cast(replicate(cast(0x12 as varbinary(max)), 524288) as varbinary(max)) as RawData from sys.messages

I use GetFieldValueAsync<Stream> to get the column value, then call ReadAsync in a loop, summing up the bytes read. This usually sums to 524,288 bytes/row, as expected. However, sometimes this actually returns fewer bytes than expected. It looks like it actually returns [Configured Packet Size] - 12 fewer bytes, but I'm not sure if that holds for every value.

This always occurs for rows at the same position, even when all of those rows are identical. Those positions vary depending upon the configured packet size though. My test results were:

Packet Size Rows
512 Row 14 onwards; every 41 rows
768 Row 39 onwards; every 40 rows
900 Rows 107; 137; 167; 197; 227; 400; 430; 460; 490
1024 Row 14; every 91 rows
1500 Rows 15; 316; 361

To reproduce

public sealed class SequentialAsyncStream
{
    public static string ConnectionString { get; set; } = "Data Source=tcp:localhost;Integrated Security=true;Encrypt=false;Trust Server Certificate=Yes; Packet Size=512";
    public static async ValueTask Test()
    {
        const string CommandText = "select top 512 cast(replicate(cast(0x12 as varbinary(max)), 524288) as varbinary(max)) as RawData from sys.messages";
        using SqlConnection conn = new SqlConnection(ConnectionString);

        await conn.OpenAsync();

        using SqlCommand cmd = new(CommandText, conn);
        using SqlDataReader rd = await cmd.ExecuteReaderAsync(System.Data.CommandBehavior.SequentialAccess);
        int i = 0;
        byte[] buffer = new byte[524288];

        while (await rd.ReadAsync())
        {
            int totalBytesRead = 0;

            i++;
            using (Stream strm = await rd.GetFieldValueAsync<Stream>(0))
            {
                int bytesRead = 0;

                while ((bytesRead = await strm.ReadAsync(buffer)) > 0)
                {
                    if (buffer.AsSpan().ContainsAnyExcept((byte)0x12))
                        Console.WriteLine("Row {0}, unexpected contents", i);
                    totalBytesRead += bytesRead;
                }
            }

            if (totalBytesRead != 524288)
                Console.WriteLine("Row {0}, bytes read: {1}", i, totalBytesRead);
        }

        Console.WriteLine("Rows: {0}", i);
    }
}

await SequentialAsyncStream.Test();

Expected behavior

In my reproduction, every row should contain one field with 524,288 bytes and this should always be read from the stream in full.

Further technical details

Microsoft.Data.SqlClient version: 6.1.1; 6.0.2; 5.2.3; 5.1.7; 5.0.2
.NET target: .NET 9.0
SQL Server version: SQL Server 2019 (local); SQL Server 2025 (local); Azure SQL (UKSouth)
Operating system: Windows 11

Metadata

Metadata

Assignees

Labels

Triage Done βœ”οΈIssues that are triaged by dev team and are in investigation.

Type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions