Blitz Result: Full Recovery Mode Without Log Backups

When a database is in Simple Recovery Mode, SQL Server does circular logging: it goes back to the beginning of the transaction log and reuses space when it can.  Portions of the log are freed up when they’re not covering open transactions.

On the other hand, when a database is in Full Recovery Mode or Bulk Logged Recovery Mode, SQL Server doesn’t free up the log file when your transactions finish.  It will continue to grow the transaction log, thinking that you’ll want to back up all of these logs at some point.  (You can learn more about Recovery Models from Books Online.)

This part of our SQL Server sp_Blitz script checks to see if there’s any databases in Full Recovery or Bulk Logged Recovery that haven’t had a transaction log backup in the last 7 days.  Granted, you should be doing ‘em much more frequently than that, but we gotta start somewhere!

To Fix the Problem

We have a couple of choices here: we can either put the databases in simple recovery mode (meaning SQL Server will start doing circular logging) or we can start taking transaction log backups.  Talk to the business users to find out whether they want point-in-time recovery for these databases (in which case you want do to log backups) or if the users are okay restoring to the last full backup if the server fails.

Return to sp_Blitz or Ask Us Questions

To learn how to properly perform backups and restores, check out our Backup & Recovery Step by Step training.

css.php