4

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

6
  • 2
    Regarding "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)?" -> this might need a giant disclaimer that you need to test query performances before pushing to prod, but.... have you tried partitioning? Can you even partition the table (if this is data life cycling for an off-the-shelves app, you might not)?
    – GMassDBA
    Jul 8, 2020 at 11:09
  • 2
    Assuming you are using the FULL recovery model, the transaction log will continue to grow until the log is backed up. You'll need to either backup the log more frequently or increase the size to accommodate all the transaction activity between backups.
    – Dan Guzman
    Jul 8, 2020 at 11:19
  • 3
    In the case of SIMPLE, committed log space up to the point of the oldest transaction will be reclaimed during checkpoint. This typically occurs every minute so perhaps you are exhausting the log space before the automatic checkpoint or there's a concurrently long-running transaction. You could try executing an explict CHECKPOINT after each iteration of the second query WHILE loop to expedite the log truncation.
    – Dan Guzman
    Jul 8, 2020 at 11:28
  • 2
    What isolation level are you using? p.s. welcome to the forum!
    – Vérace
    Jul 8, 2020 at 11:31
  • 2
    How are you executing this? Sounds like it wraps it all in one tran Jul 8, 2020 at 13:28

2 Answers 2

6

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.

3
  • Thanks for the suggestion. Yes, I was using SSMS to test various iterations of the query. I checked the SET IMPLICIT_TRANSACTIONS option, but, as I would expect, it is not enabled.
    – Tom Lint
    Jul 8, 2020 at 12:20
  • 1
    Then you need to dig deeper. sys.dm_exec_sessions has columns such as open_transaction_count and transaction_isolation_level, for instance. So, before doing the ROLLBACK which rollbacks everything, check if that session has a transaction open. Or you can even use @@TRANCOUNT. Make sure you are 100% certain, no doubt what so ever, that you can rollback the lot with your "batched" code. Next step for you depends on this! Jul 8, 2020 at 12:54
  • @Tom Lint - hey did you get a solution for this? Facing the same issue. I have tried out all that's mentioned. If you got the solution , can you pls help me out with that? Aug 16, 2021 at 8:05
0

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.

2
  • 1
    There's a fundamental misunderstanding here, since cancel behaves the same way for a while loop (unless only the while loop is wrapped in a single transaction, as has already been suggested in the comments three years ago). Additional context for readers - the OP is insisting vastly different performance between a while loop and GO n. I don't think we're getting the whole story. Aug 26, 2023 at 15:32
  • Go and While cannot work the same. Because GO is a quasi client side operation. it's as if your while loop is from a C# or PHP app. If you kill it, it will only try to rollback the last iteration. Whereas the while loop is a server side loop. If you are running a small site, you will have full access to tune the server for one app. on large size, the server is shared and corporate lvl decides.
    – e-Fungus
    Aug 28, 2023 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.