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)?