SQL rolling back all iterations of a while loop when each iteration has its own explicit transaction

Question

For the past few days I've been trying to write a query for an automated process that will delete records from a log table within a specified date range (a month) to another table, so they can then be dumped to disk and the records deleted from the database. A first, naïve approach, á la

DELETE FROM <sometable>
OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}

of course blew up the transaction log. I then tried doing the same, but now in batches of 1m rows, as shown below

WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (1000000) FROM <sometable>
    OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
    WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}
END

but that did nothing to change the transaction log growth. Next, I tried using an explicit transaction within the loop.

WHILE @@ROWCOUNT > 0
BEGIN
    BEGIN TRAN

    DELETE TOP (1000000) FROM <sometable>
    OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
    WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}

    COMMIT
END

Again. No difference. What's worse, if I cancel this query and do a ROLLBACK to release the table, it rolls back everything. Not just the last iteration, as one would expect, but the whole thing. Why aren't the individual transactions within the loop being treated as such? Surely there must be a way to move huge amounts of rows around the database without SQL throwing a hissy fit and blowing up on its user(s)?

Answer 1

The key here is this:

What's worse, if I cancel this query and do a ROLLBACK to release the table, it rolls back everything. Not just the last iteration, as one would expect, but the whole thing.

So apparently your work was already transaction protected. Breaking it up to several DELETE did noting for you, since apparently they are all still one transaction. You need to find out why. You don't say from where you are executing your code.

If you use SSMS, then perhaps somebody turned on SET IMPLICIT_TRANSACTIONS ON. This turns off autocommit. Can be done in a config option in SSMS or using above command. But then you would have to commit your work when you are done, or SSMS would catch that you have an open transaction and ask if you want to commit or rollback.

Perhaps you use some other environment that will protect your work and submit rollback if SQL Server throws an exception. this is where I would start. that would explain everything - but there's some more digging from your side to do.

Answer 2

I am using GO instead of looping

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go

DELETE TOP (1000000) FROM <sometable>
OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}

GO 500000

This will run 500000 separated transaction. If you stop, the roll back will be just for 1000000, not the whole while loop.

The downside is I have to calculate manually the go count.