Sql-server – SQL Server bulk delete

sql server

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.

Best Answer

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
Related Question