Deleting 18 Million Rows in Azure SQL Without Downtime
Mitchel Sellers describes how he purged over 18 million historical rows from an S1-tier Azure SQL database by deleting small, committed batches instead of one large statement. The article covers batch sizing, clustered-key range selection, supporting indexes and production monitoring. It is a practical guide for any .NET developer planning a large data cleanup on a shared database.
Key points
- Use fixed-size batches instead of one large DELETE; the author's values were 25,000 rows roughly every six seconds.
- Select batches by bounded clustered-key ranges and advance the checkpoint based on the range examined.
- Verify with the actual execution plan and logical reads whether a supporting index on the date predicate is worth its write cost.
- Monitor CPU, data I/O, log I/O, blocking and batch duration, and reduce batch size or pause when thresholds are crossed.
- After the purge, judge index fragmentation together with page density and query behavior, not by percentage alone.
- Turn retention into a scheduled, observable job that removes small amounts regularly instead of one-off emergency cleanups.
Mitchel Sellers needed to purge more than 18 million old activity rows from an Azure SQL database on the S1 tier while the application kept running. A single giant DELETE would have bloated the transaction log and held locks too long. Instead, he removed about 25,000 rows roughly every six seconds, committing each batch separately.
The key is making each batch cheap to find. Working through bounded clustered-key ranges and advancing the checkpoint by the range examined avoids rescanning the table. A date-based supporting index may help, but it adds write cost, so verify it with execution plans and logical reads. Keep watching CPU, log I/O and blocking, be ready to slow down, and judge index fragmentation alongside page density.
Real-world use
- Purging old log or activity rows from a shared Azure SQL S1 database that also serves a live application.
- Applying a retention rule to a years-old table so historical data is removed as background work without blocking user traffic.
- Running a checkpointed, cancellable bulk delete from a .NET background worker that uses EF Core or raw SQL.
Why it matters
Splitting a large delete into controlled, committed batches shortens lock time, limits log growth and protects application latency. Running retention as a scheduled job also prevents the next emergency purge.