Deleting large number of rows without filling the transaction log

Sometimes, when you try to delete a large number of rows from a table the transaction file may grow to the point that you will run out of disk space. In other occasions, when the transaction log growth is limited, the delete can fail with the following message:

The transaction log for database ‘database name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
There are several ways to overcome this issue:

1)    If you want to delete all rows in the table, consider using TRUNCATE TABLE instead of delete. Truncate will not fill the transaction log. Remember that TRUNCATE will also reset any auto incrementing keys.
2)    In case you need to keep some of the data in the table, depending on the ratio between the numbers of rows you are keeping to the number of rows you wish to delete, you may want to:

a)    Copy to the rows you want to keep to another table

b)   Truncate the original table

c)    Copy the data from the temporary table back to the original table.

3)    Delete manually in chunks. You can run: delete TOP (10000) from [your big table]. Remember to commit between deletes.

4)    Use script to delete in chunks with commit every number of rows.
This script for example commits every 100000 rows. Change it to fit your specific scenario:


DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT
SELECT @chunk_size=100000
SELECT @Count = 0
select @for_delete=count(*) from [Your big table] where  [Your Where Clause]

While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGIN TRAN
DELETE top(@chunk_size) FROM  [Your big table] where  [Your Where Clause]

COMMIT TRAN
END

5 thoughts on “Deleting large number of rows without filling the transaction log

  1. Pingback: DB Tricks » Adding a Transaction Log at runtime to prevent it from running out of space

  2. Pingback: Bulk delete in MS-SQL server | Ryslander.com

Comments are closed.