I am trying to clear out a large amount of data out of a SQL Server table. In SSMS, I run this:

DECLARE @CutoffDate DATETIME = DATEADD(DAY, -30, GETDATE());
DECLARE @BatchSize INT = 10000;

-- Loop to delete dependent records in batches
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;

    DELETE TOP (@BatchSize) FROM ChildTable
    WHERE ID IN (SELECT ID
                 FROM ParentTable
                 WHERE LogDateTime < @CutoffDate);

    IF @@ROWCOUNT = 0
    BEGIN
        COMMIT TRANSACTION;
        BREAK;
    END

    COMMIT TRANSACTION;
END

It’s been running for hours, but in a separate session I’m able to query progress with

SELECT TOP 1 LogDateTime 
FROM ChildTable 
ORDER BY ID

But I’d like to create a job that keeps the table empty, so I put that query into a SqlCommand and executed it asynchronously.

Now, I am no longer able to check on the progress with my simple SELECT statement. It’s like SqlCommand has made its own transaction and/or locks that SSMS did not.

Does anyone know what is happening?

Note: I’m not asking for a solution. I can run the query through SSMS or use a C# loop. I’m more interested in why they execute differently.