At my work, I am going to be moving millions of records to a different server, and removing them from the source server. My DBA has set up a backup scenario where change backups are taken every 5 minutes. He had this warning for me regarding deleting the records:

...it is going to create a gigantic transaction log that will probably cause the 5 minute transaction log backups to fail...

Are there any ways to get around this?

  • What version of SQL Server are you on? – WEI_DBA Sep 23 '16 at 16:27
  • 2
    Batching your deletes will help a great deal. I don't see anything in your question about already doing that, so here's my favorite resource: michaeljswart.com/2014/09/take-care-when-scripting-batches – Erik Darling Sep 23 '16 at 18:35
  • Suggest you post you move the (copy) delete tsql. Insert millions is also a problem. If you are moving them all then just truncate after they are moved. Truncate has minimal logging. – paparazzo Sep 23 '16 at 21:53

We don't know why your transaction log backups will fail but we can discuss solutions. He could be concerned about transaction log bloat, when the transaction log would grow so large that it filled the drive where the log file is stored.

First, and easiest solution: If you're moving all the data out of the table entirely, simply truncate the table. This is minimally logged.

Second: Split your DELETE statement into batches. Find a way to divide your data, let's say in batch sizes of 5,000.

          (SELECT TOP 5000 ID 
           FROM OldDBTable 
           WHERE [Statement to define deleted data here])

You could probably increase that batch size if you want. Then just set a WAITFOR DELAY '0:05:00' to wait 5 minutes for your next batch.

  • 1
    I'd definitely recommend testing to find out your ideal batch size - it can make a huge difference in speed and resource usage even when batching. – LowlyDBA Sep 23 '16 at 16:36
  • 1
    Sorry @Arthur D. I saw your answer after posting mine. Because both of us are on the same page. – Ashwini Mohan Sep 23 '16 at 16:44
  • @JohnM great point! Splitting millions into chunks of 5000 is fairly slow. I didn't want to provide a baseline recommendation that could cause a lot of blocking but Jeff should definitely test to find the ideal amount of records to delete. – Arthur D Sep 23 '16 at 17:25

Taking transaction log back has almost no effect on ongoing activity and no relation with them so log backup won't fail.

I think your DBA is more worrying about increasing transaction log size while deleting millions of records.

If that is the case, you can discuss with him and try moving and deleting records in small batches instead of moving and deleting them in a single transaction.

You can go through this link: https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

But please confirm first with your DBA.

  • He is indeed. I think we are going to move the records we want to keep (only the most recent couple of days), and then truncate. – Jeff.Clark Sep 25 '16 at 2:19

I recently performed a similar task with minimal overhead. I coordinated with my sr. DBA to

  1. Identify and store the records you want to keep select * into foo_table from table where <keep_condition> = 'true'
  2. Disable any or drop any relevant constraints ( making sure you can recreate them, of course )
  3. Truncate the target tables
  4. Re-insert from foo_table ( watching out for computed/identity columns )
  5. Clean up

Disclaimer: Obviously it's best to do this with plenty of rollback ability and during a downtime. But if you can get the downtime to do it, it's not a headache at all

  • Right on. Thank you for your input. It seems the consensus is to truncate. – Jeff.Clark Sep 25 '16 at 2:19

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.