The topic of truncating SQL transaction logs is a regular point of confusion. Two issues normally arise, and are the reason for this post.
Transaction Log Full Error
For anyone working with SQL long enough they have undoubtedly encountered the following error in an application, integration, reporting query, etc.
The transaction log for database 'db_name' is full due to 'LOG_BACKUP'
This error occurs when the transaction log is full and can no longer grow for various reasons.
Transaction Log Consumes Giga/terabytes of space
In some cases, the above error is never encountered because the drive that the transaction log resides on either contains enough space to allow the log to grow. Whether the drive was pre-sized or a system administrator expands it, this can lead to huge transaction log sizes. In some cases, the transaction log may be GB or TB larger than the entire database itself!
Below are two options to address these issues.
Option 1 | Simple Recovery
One way to make sure that the SQL transaction log does not grow out of control is to set the database to SIMPLE recovery model from FULL recovery model. The SIMPLE recovery model truncates the transaction log once a transaction completes.
The benefit
Since the log is being truncated as transactions are performed, the log never grows out of control.
The downside
Due to the transaction log being truncated in "real-time", it is only possible to recover to the last full backup performed. This works for some use cases and not for others.
Cases where this works well
Data warehousing (that can be completely rebuilt from scratch)
"Scripting" databases that only hold scripts/functions
Development and test environments
Cases where this does not work well
Production
Application databases (OLTP)
Data warehousing that cannot be rebuilt from scratch
Option 2 | Backup Transaction Log
The second option to ensure the transaction log does not grow out of control is to properly backup the log. It is suggested to use SIMPLE recovery when it makes sense. With that said, this is not always possible as outlined above. If FULL recovery should be used, backing up the transaction log regularly will truncate the log. Note, this is backing up the TRANSACTION LOG itself, not backing up the data. While backing up the transaction log should be a part of any disaster recovery plan, sometimes only the data is backed up with full backups. This will lead to the transaction log growing out of control and encountering the issues mentioned at the beginning of this post.
Conclusion
Transaction log management is easy to implement using the various options above, yet easy to get wrong or overlook. Make life easy and use simple recovery or backup the transaction log to ensure the log size does not grow out of control.
Comments