SQL Server – Understanding LOG_BACKUP as Log Reuse Wait Reason

Few suggestions from my end, Kindly check and let us know

1. Run SQL Profiler and check for any unusual activities.
2. Check if the increase in size of the log files happens regulary at or around the same time (If yes, growht in log file might be the result of scheduled job)
3. Run DBCC LOGINFO and check if the VLF's are active or inactive.
4. I randomly GUESS the result of the log growth is due to index maintenance activities you might have scheduled.
5. Lastly, Just for information: You need to change the database recovery model to SIMPLE and then perform the DBCC SHRINKFILE command.

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.