4

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

3 Answers 3

5

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.

2
  • Thank you so much for clear explanation.
    – anu
    Mar 11 at 19:21
  • @anu Absolutely, no problem!
    – J.D.
    Mar 11 at 23:02
1

I would do this modification during the period where there is as less transaction as possible (be carefull if you think about doing it during off hours if you have maintenance job that also run during this period. Index rebuid can generate a lot of log).

Once the full is done, log backup should be taken regularly (depending on the activity and your RPO (the amount of data you can lose), the frequency may vary from every minute to hourly log backup).

Finally, I would make sure there is a lot of free space on the log disk (and maybe have another disk accessible just in case) and that the auto-growth is enable for the log files

1
  • Thank you for the key point to note regarding off business hours and also during index maintenance.
    – anu
    Mar 11 at 19:23
0

Once you set it to full the log will only be truncated until you take a log backup. In simple mode SQL Server truncates the log file on its own. In full recovery mode it is not dependent on how long the full backup takes or if you take one or not. The log is truncated (freed up) when a log backup is taken. Waiting five hours on a busy database (which your db seems to be) may result in a large log. If your log is not set to automatically grow or it runs out of space on your storage device the DB will stop and you will likely get a 9002 error.

2
  • To be in "real" full recovery model, a first full backup needs to be completed doesn't it ? This mean that before the first full backup completes (5 hours), any log backup would probably not truncate the log file doesn't it ? Mar 10 at 19:13
  • Thats true . so after changing it to full recovery model. i wont enable log backup until 5 hours or until the backup completes.
    – anu
    Mar 11 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.