Deleting Large Number of Records from a Database Table

Things to remember when deleting a large number of records from a database table.

• Consider using TRUNCATE instead of DELETE if truncating the table (removing all records from the table) is ok.
• Remove records in batches.
• Make sure there is enough space for the log files.
• If database is simple, truncate log after every run. If full, run transaction log backup and shrink the log.

Note: Highly recommend to delete records in batches. If you delete everything in one transaction, if anything fails (for example, ran out of log space), you will have to roll back the entire transaction. Ran delete in batches and clear the log file after each batch to avoid running out of space, even in Simple database backup plan.