Managing size of Transaction Log in SQL Server

During previous sprint I was asked to investigate why the file of Transaction log on production SQL Server becomes huge over time. The situation indeed looked strange: the size of data file was just about 5.5 GB, while the size of Transaction log file was more than 150 GB.

Here are the actions I performed to investigate and fix this problem.

Quick research revealed the most probable root cause of Transaction log increase – absence of regular log backup while the Database is in Full recovery model. In this case (as described in detailed StackExchange answer) transaction log should contain all database operations since last backup, so that recovering to specific point in time could be provided, as Full recovery model requires.

Database recovery model could be determined with the following query:

Now we know that database is indeed in Full recovery model. Then I’ve asked about backup plan used for this database. It turned out that backup was performed by custom PowerShell script, scheduled every 6 hours. Once every ten days the script was creating Full database backup with following command:

For all other launches the script was creating incremental backup with following command:

Now we see that no transaction log backup was ever created, only full and incremental backups were performed regularly. As this topic explains, neither Full or Differential backups truncate the transaction log.

So the assumed root cause confirmed. Two fixes are possible to prevent growth of Transaction log over time:

  1. Switch database recovery model from Full to Simple.

Simple recovery model does not support point-in-time restore and could only recover to the latest backup, while all operations made afterwards will be lost. I believe Simple recovery model is not suitable for big production Database, that’s why this option was rejected.

  1. Perform periodic backup of Transaction log.

This is the only choice to truncate Transaction log in Full recover model and keep it from getting huge over time.

Finally, to fix the problem following actions were performed:

  1. Existing backup script was adjusted so that it backed up Transaction log instead of making differential backup.

Backup of Transaction log could be created by following simple command:

  1. I’ve also suggested increasing of backup frequency from once in 6 hours to once in 15 minutes. It shouldn’t affect SQL Server load because dump of Transaction log is a light operation. At the same time such frequent backup schedule minimizes probability of data loss or at least scale of such loss.
  1. One more required step is Shrinking of current huge Transaction log.

After Transaction log is backed up, SQL Server truncates it, however it doesn’t mean that file size will be reduced to zero. Truncation means only logical deletion so that new operations could be written at space occupied by released entries, while the physical reducing could be achieved by operation of Shrinking.

As explained here and here, shrinking should not be performed under normal circumstances. However we’ll have to shrink current Transaction log once. After this initial shrinking, regular Transaction log backups will keep the log in normal size.

This plan was executed on Production Database and after some time, I was informed that the problem is fixed and Transaction log is not increasing in size anymore.

Here are the sources that helped me to investigate and fix this issue:

This entry was posted in Programming and tagged . Bookmark the permalink.

Leave a Reply