I have a large table (600 million rows, 60GB data) that I need to migrate to another table. This is for a few reasons:

  • The current table is a heap and I want to convert it to having a clustered index
  • I have a column which is time(7) that only really needs to be time(4)
  • The table has previous deleted columns that are still reserving space from what I can see (they are just marked as is_dropped in the metadata.

The best way I could come up with doing all this in the fewest operations (i.e. not having to rebuild the table multiple times) was to create a new table with the column definitions I want and copy the data across into the new table while deleting the old data, followed by a name switch. I've split this into batches in a loop to prevent the log file growing continously, however this doesn't seem to be working and after about 70million rows, the log file has grown to around 110GB and there is no space left on the server...

The database is using simple recovery and I have a checkpoint after each batch, which I thought was meant to stop the log file growing! Am I missing something, or is there some other reason my log file continues to grow? This is testing on a dev server I'm doing, so there are no other operations currently running on the table.

My query is:

WHILE 1 = 1
    DELETE TOP(1000000) FROM RunningData with (tablock)
    INTO [dbo].[TmpRunningData] (

    RAISERROR ('Moving to next batch', 0, 1) WITH NOWAIT
    waitfor delay '00:00:01'

I really want to be able to sort this table out, but I'm struggling to find a way of processing this table due to the size! Any suggestions on how I can stop the log growing, or another way to do it would be very appreciated, thanks! In production the database can go down for a weekend, so there should be plenty of time to do it.

  • 1
    I tried to reproduce on my server and the checkpoint is clearing the log. Have you try with smaller batches ? – Dominique Boucher Oct 14 at 14:31
  • I agree with @DominiqueBoucher, try using a much smaller batch size, and then see how much the log is growing. Depending on how wide your table is, 1million rows could be quite a lot of data. – BCM Oct 14 at 14:43
  • Sorry, I should have added originally, I have tried different batch sizes - I was originally using 100k at a time which also saw the issue after about the same period of time - I would have thought if the transaction log can handle one batch, it should be okay looping through with the checkpoint though? – Dutchmanjonny Oct 14 at 15:16
  • In theory it should yes... When I reproduce it on my server (with a longer wait before each batch), I can see the log getting filled and then when it hit the checkpoint it cleared itself. Give it a try with a 5 sec wait and 100 000 rows batch and let us know if you still have the issue. If you do, then increase the wait to, let's say 1 min and during this minute, look at the sys.databases like Tibor suggested in his answer to make sure there is not something else preventig the log from truncating. – Dominique Boucher Oct 14 at 15:32

First step is to ask SQL Server why the log grows. You should find the answer in the log_reuse_wait_desc column in the sys.databases catalog view. Being in simple mode, it shouldn't be log backup. Then take it from there.

And don't do it all in one transaction, since SQL Server can't truncate past the oldest open transaction.

| improve this answer | |
  • Whilst running, it is set to 'ACTIVE_TRANSACTION' for the DB, which seems expected as this is all happening within a transaction – Dutchmanjonny Oct 14 at 15:23
  • SQL Server can never truncate the log past the oldest active transaction - it wouldn't be able to rollback if it did. So if all your work is transaction protected (which we didn't see in the code you posted), then you gain nothing from your batching. – Tibor Karaszi Oct 14 at 15:32
  • Okay, I guess that's what I've missed, I assumed checkpoint worked with the transaction. I guess that means there's no way of doing a "dry" test run then and I just have to "commit" to it! Thanks for the help, this is probably why electronic engineers shouldn't be made into dbas also! – Dutchmanjonny Oct 14 at 15:44
  • Haha! Don't beat yourself up about it. :-) But, yeah, you can't have the cake and eat it, I'm afraid. If you were on SQL Server 2019 you could play with Accelerated Database Recovery, but I'm not sure it would help and possibly overkill for your case. – Tibor Karaszi Oct 14 at 15:50
  • 1
    Yeah, now I think about it logically there's no magic that can happen - if I want to rollback I have to store all the data to do so somewhere... – Dutchmanjonny Oct 14 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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