How to delete or update millions of SQL Server rows safely using batching

Running a single huge DELETE or UPDATE against millions of rows can lock a table for a long time, fill the transaction log, and make cancellation painfully slow. The recommended approach is to process rows in small batches, each in its own transaction, which keeps locks short and log growth manageable. The filter column should be indexed so each batch finds its rows quickly, and a range-based walk on the clustered key works well for large updates. Log backups must keep running under the FULL recovery model, and blocking and progress should be monitored so batch sizes can be adjusted. For some cases, TRUNCATE, copy-and-swap, or partition switching may be faster alternatives.
This is an AI-generated summary. ShortSingh links to the original source for the complete article.
Discussion (0)
Log in to join the discussion and vote.
Log in