Tag Archives: SQL Server

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

What to do when ldf file grows

If you noticed that the free space on your hard drive is shrinking and you found that the log LDF file is taking most of it (and growing) don’t panic.
it is possible that the reason is that your recovery mode is is set to full.
to change this:

1) Right click on the db (in SQL server Managment Studio) and click properties.

2) Navigate to the option tab and make Make sure that the recovery model is set to simple.

If a full recovery mode is needed:

Issue regular backups (in the transaction log shipping tab)
and if Auto Shrink is enabled, the file will eventually shrink (though not immediatly)

If you need to shrink the file immediately:

•    Open MS SQL Server Management Studio, connect to Database Engine

•    Select New Query and type:  backup log <db_name> with truncate_only

(this is discontinued in SQL SERVER 2008 – Microsoft recommends using Simple mode instead)
•    Execute this query (press F5)
•    Right click on Database name and navigate to Tasks->Shrink ->Files: