Full recovery model and transaction log



  • I will be changing a 5TB database from simple to full recovery model due to new business requirement. The database has been in simple recovery model since 5 years.

    My question is once I change the recovery model, full backup will be taken. But how to make sure the log does not grow for the database. Because estimated backup completion time is approximately 5 hours. I am thinking to create a job and schedule it such that the log backup starts after 5 hours. Is this a good method? Please advise. I wanted to make sure until the full backup of the database which just changed to full recovery model is completed, logs should not grow.

    Thank you



  • When a database is in the Full Recovery Model, the Transaction Log continues to fill up and will grow once the Transaction Log file is full provided there is space available on the disk for the file to grow. If there isn't any space left for the file to grow, then you'll receive errors in your SQL instance when you try to run a transactional query (regardless if it's implicitly or explicitly transactional), which will be most queries.

    When you take a Transaction Log backup, SQL Server marks the consumed space of the Transaction Log file as re-usable. This will allow the bytes of the log file to be overwritten instead of continually being appended to, and will help minimize growth of the log file.

    This is independent of your Full Backups and when they finish. Now you will see errors in the SQL Server event log or job history log for when your Transaction Backups are running until the first Full backup completes. This is because you can't restore those Transaction Log backups without a Full backup that precedes them and are part of the same backup chain. But once the Full backup finishes, any subsequent Transaction Log backups will be usable and that error will go away automatically.

    Given the above, most people schedule their Transaction Log backups to run at a frequency for the level of point in time recovery granularity that they need. Most times this is anywhere between every 1 minute or every 1 hour. I personally run them every 5 minutes, because it gives me better point in time recovery (less chance of data loss) and results in smaller backup files, so they complete the backup process a lot quicker (usually within a second). It also more quickly releases the log file for those logs to be overwritten, helping maintain the log file without it growing out of control.

    Long story short, my advice would be to schedule your Transaction Log backups to run at the frequency of granularity you need for point in time recovery. The more frequent you can schedule them to run, the less likely your log file will grow out of control.

    But please note, there's no perfect solution. Even a really large explicit transaction can cause your log file to grow, regardless of how frequently you back it up. That's just not usually the norm, and if it is, then the size that the log file grew to is an appropriate size that it should be kept at.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2