Sql-server – How to Fix log_reuse_wait_desc=LOG_BACKUP? LOG Backup not possible

backuplogsql servertransaction-log

I checked sys.databases to find out which databases need LOG backups. It surprised me that I had quite a few where log_reuse_wait_desc=LOG_BACKUP meaning, the JOB I have set up to run LOG backups on these databases isn't actually creating LOG backup files.

After doing some searching, it had been suggested to change database modes from FULL to SIMPLE, then back to FULL to reset things. After doing this I am still unable to get a LOG backup to successfully run.

I am using SQL Server 2005, and use the ola hallengren method of backup found here http://ola.hallengren.com/sql-server-backup.html.

Thank you

Best Answer

Now that you've changed the recovery model on the database from full to simple and back to full again, the database is running in 'pseudo-simple' mode. Taking a database backup is required now before a log backup will do any meaningful work. See here for some further details. To summarize:

In order for a log backup to be useful for a restore, there needs to be a database backup along with log backups that cover all the LSNs since the time of that database backup.
When you change the recovery model to simple, SQL Server breaks the log backup chain. Then when you change it back to full, the database continues to act like it is in simple mode until someone creates a new log backup chain by taking a database backup. This is known as pseudo-simple mode. The reason this is implemented this way is to prevent someone from taking log backups that could never be used for recovery because they aren't part of a log backup chain that begins with a database backup.
Once a database backup is taken, a new log backup chain begins and the database begins functioning in the full recovery model. At this point your log backups should work, or at least fail for another reason with a more descriptive error.

This will be at least one reason why your log backups aren't functioning now.

Related Solutions

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

SQL Server 2008 R2 – First Log Backup After Full Backup Takes Up Space

Taking a FULL backup won't clear out the transaction log entries (unless the database is in SIMPLE Recovery Model). So whatever changes take place between your last LOG backup before the FULL backup (@ 4:30 AM), and the first LOG backup once that process is over (@ 6:30 AM) would be included in that 6:30 AM LOG backup. That is a 2 hour span (you typically do a LOG backup every 30 minutes otherwise) in which you, at the very least, run index maintenance via the IndexOptimize stored procedure.

If you want to test this out, just temporarily disable both the "IndexOptimize" and "Database Integrity Check" steps (meaning, if you are using the CmdExec step type so it can call SQLCMD, just add REM to the beginning of the line in both steps). Let that run for one night and if I am right, it will be very obvious. Then, remove the REM from one of the two steps so that only one of them runs again (maybe start with the "Database Integrity Check" step) and let that run for one night and then you should see how much impact each of those first two steps has on that "first" LOG backup.

If the "Database Integrity Check" step is taking up more than a small portion of that LOG backup, then you might be able to split that initial backup into two (even if not an even 50/50) by doing a LOG backup after the "IndexOptimize" step. If all (or most) of the LOG backup size is from the "IndexOptimize" step, then maybe try to schedule a LOG backup for 4:36 AM to attempt to cut the resulting LOG backup in approximately half (given that you show that first step running for just under 14 minutes).

Best Answer

Related Solutions

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

SQL Server 2008 R2 – First Log Backup After Full Backup Takes Up Space

Related Question