When we use DELETE operations while we need to remove some rows, sometimes we have the intention to make our database smaller in size. It’s a little odd to think that deleting rows can make our database bigger, so our task is to avoid transaction log growth.
What can cause DELETE in SQL Server?
You use delete to remove some rows or all the rows in a table. The problem comes when you see your transaction log growing and growing and seems not to stop until you get a beautiful message like this:
Msg 9002, Level 17, State 4, Line 2
The transaction log for database ‘DBname’ is full.
If you get this error message and you can see your disks without empty spaces and datafiles grown, you have only two options.
How to avoid transaction log growth?
- Change (or tune) the query
- Add empty space to disk so it can grow more
In a scenario with limited storage resources (and even if you do have resources), the best way is always to improve the queries, tune them properly and optimize what they do.
In this particular example where you need to delete several rows, the DELETE syntax makes the elimination row by row making your transaction log grow until the operation ends. Sometimes this growth is not easy to handle.
A perfect alternative for DELETE
To avoid the transaction log growth, you can remove rows by batches within a WHILE cycle. This will help you to reduce the work of your transaction log and the unpredictable growth will dissapear.
WHILE 1 = 1
DELETE TOP (1000)
WHERE flag = 0
IF @@rowcount < 1000
That WHERE condition is only an example you can replace with your own condition depending on your needs. The TOP number of rows also can be bigger depending on if you need a faster response or smaller if you still get the transaction log message error. As always Testing is your best bet.
Additional considerations to avoid transaction log growth
DELETE is by definition a ‘fully logged’ operation so it will always have work with transaction log. If you want to delete all the rows in a table, a better move will be to use a TRUNCATE operation.
Removing rows in a table always gets an exclusive lock so you need to be careful in high concurrent environments.
Personally I find no sense in the use of DELETE in a transactional application. If you have a transactional table where all the time you are saving new data, it looks like a place where you need to save not remove. Also because all the considerations in this article. Maybe a best approach can be an UPDATE operation where you change an ‘active flag’ from 1 to 0, even for auditing purposes or for maintaining historical references.
Avoid the transaction log growth is so important to avoid further unexpected systems downs. Also you need to remember that the growth rate of a datafile can make disk contention depending on sizes and autogrowth settings.