Quick Tip: SQL Transaction Log Management

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

  1. Data warehousing (that can be completely rebuilt from scratch)

  2. "Scripting" databases that only hold scripts/functions

  3. Development and test environments

Cases where this does not work well

  1. Production

  2. Application databases (OLTP)

  3. 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.