4

I had to delete around 600K records daily from a table. The problem I am facing is that whenever delete happens, the log database size increases to 1 GB. Even after the delete operation has been done, log database size doesn't shrink to its normal size. This is a problem as log DB size is increasing to 1 GB every day.

I cannot use truncate as table to be deleted is being referenced by other tables.

1 Answer 1

13

When you perform a transaction - like deleting 600K records all at once - SQL Server has to log what it's doing in the transaction log file as it works.

Say the process takes 5 minutes, and 4 minutes into it, the server reboots for some reason. SQL Server starts up, opens the log file, and then needs to undo all that work (because the transaction never committed.

To avoid having a log file that large, you can:

  • Do your work in smaller transactions - like working in batches
  • Put your database in simple recovery model (so that transaction log space is available for reuse as soon as your transaction is committed), or
  • If you're in full recovery model, back up your transaction log frequently, maybe even every minute - because the space is only marked as available for reuse after it's been backed up
2
  • Is Backing up the db every minute can have a bad impact on the delete process .
    – itzik Paz
    May 26, 2018 at 14:54
  • @itzikPaz to find that out, you'll need to do load testing. If you find that the frequent backups are too much overhead, then you'll need the transaction log space while you work.
    – Brent Ozar
    May 26, 2018 at 17:00

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.