Menu & Search
Batch Deletions in SQL Server

Batch Deletions in SQL Server

This script will delete rows within a table that are out-with the specified retention period. This can be useful if you’re running deletes on large tables, and particularly if;

#– Deletes are taking a long time & you’re finding it difficult to track progress.
#– You have a short maintenance window each day to perform such tasks, which could be due to caution over blocking/deadlocks.
#– Your server is not equipped to handle the log file bloat associated with running the delete as a single transaction.

Whatever the reason is, this is a useful script to have in the toolbelt.

I’ve set this to delete 10k rows at a time, although you can find your own sweet spot.

To find out how long it’ll take until all rows have been deleted, you could do something like;
a. Get row counts.
b. Run deletion script for 10 minutes.
c. Check how many rows were affected.
d. Refresh Disk Usage by Top Tables report / get new counts.
e. Compare numbers above.
f. If the math works out to be 9 months before it’ll finish, then get an index added.