Log Reuse Waits Explained: LOG_BACKUP

From:http://sqlity.net/en/1830/log-reuse-waits-explained-log_backup/

Introduction

There are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: LOG_BACKUP

Recovery Model

SQL Server knows three recovery models: Simple, full and bulk-logged. The main purpose is to influence how much data would be lost during a disaster. The recovery model it is a database wide setting.

Simple

If your database recovery model is set to simple, you can take full and differential backups. When disaster strikes your only option is to restore the last backup you had taken. Everything that happened in your database since then will be lost.

You can alleviate that by taking backups more frequent, but that can cause a higher strain on your resources.

Full

If you have set your database to recovery model full and you have taken at least one full or differential backup since, you have one more option. If the disaster for example affected the drive with the data files, but the log files are still readable, you can take a tail-log backup to capture all changes that happened after the last backup. With that you can restore right up to the point when the disaster started and you are not losing any committed data.

However, this flexibility comes at a price. To be able to offer that backup restore capability SQL Server will not reuse any part of the transaction log, until you have taken a log backup of that part. So, to be able to reuse your virtual log files you need to regularly execute a log backup. A full or differential backup does not have any effect on log reuse.

There is an additional advantage to having this log backup chain. Because the log is written sequentially, you can now tell SQL Server to stop a restore process at any given point in time. With that you can restore your database to just before the accidental delete without where clause was executed.

You also have two independent backup channels. While you have to start every restore with a full backup, is does not need to be the most recent one if you still have a complete log backup chain since that backup. The restore will take longer this way, but if the most recent full backup was damaged this can get your data back.

Bulk Logged

For the purposes of this discussion the bulk logged recovery model is very similar to the full recovery model. The only difference is, that in this recovery model actual bulk operations like the BCP command prevent point in time restores to any time that is covered by a log backup containing the bulk operation fully or partially. Also, log backups might execute slower with this recovery model depending on the size of your database and the operations performed.

Waiting for the Log Backup

As I already mentioned above, if the database is in full recovery model, the virtual log files cannot be reused if they have not been backed up yet. If this is currently the case in a database, the log_reuse_wait_desc column will report a value of LOG_BACKUP.

Solving this problem is simple: Take a log backup.

If the business does not require the point in time restore capability and can afford losing (depending on your backup schedule) a day's worth of data you can also switch the database to the simple recovery model. This might be appropriate for example for development databases that can be recreated.

Summary

If your database recovery model is "full" SQL Server cannot reuse virtual log files unless all contained transaction log records have been backed up with a log backup. If a log backup is outstanding the log has to grow to accommodate new data changes. During this time you will see a log_reuse_wait_desc of LOG_BACKUP. Full or differential backups do not backup all transaction log records, so you need to run an actual log backup to allow for virtual log file reuse.