SQL Transaction Logging
By default, any operations in SQL Server such as inserts, updates, and deletes are stored in the transaction log. The principle behind the transaction log is to revert the operation if an error occurs and to ship the logs for backup purposes. When you have a huge table and you initiate a delete command on all of the rows, SQL will delete each row after logging it into its transaction log.
Logging takes a long time and the log file will grow as each record is deleted from the database. If an error is encountered, even at the last row to be deleted, all of the transactions will need to be reverted. The process of reverting the delete operations in itself takes a significant amount of time.
SQL Server 2005 and grater enhanced the functionality of the TOP operator. It can be used in conjunction with insert, update, and delete statements. By leveraging the top function and deleting a limited set at a time, we can minimize the amount of records to be reverted in case of an error, issue less locks on the table making it more usable while the operation is taking place, and allowing the log file space to be re-used.
The following code performs a batch deletion of records 10,000 at a time:
While (select count(*) from TABLENAME) > 0
Delete Top(10000) from TABLENAME
If @@rowcount == 0 break
Just replace TABLENAME with the name of the table you are deleting and run the statement against your database.