1

I have a quick question regarding the cleaning of no longer necessary transaction log backups. So, I have a database in FULL recovery mode. Every night, at 10 PM a backup takes place (via maintenance plans). Now, I also have a different maintenance plan, that takes a transaction log backup every 15 minutes during the work hours, so at the end of the day, there are quite a few files.

What is the best way to clean these if the 10 PM backup executes successfully? Can I just delete all the transaction log backups from throughout the day? (at this point they are already caught in the backup, right?). Basically I am trying to start the day fresh, so to speak, with all the data backed-up and a new set of transaction log backups.

Thanks!

Improve this question

2 Answers 2

Reset to default
1

Have you verified the backup you took passes a VERIFYONLY, along with a full restore on a separate server passing a CHECKDB evaluation? I ask this because I've seen situations where a person took a backup, they found out that it later failed a CHECKDB, but they had removed the transaction logs for that day, so they couldn't restore up to a point in time.

Maybe, relative to the data, this isn't a big deal (ie: an ETL loader that loads a full data set that can be completely re-done if needed), but I'd highly advise keeping all transaction log backups until you've verified that the backup you have passes a VERIFYONLY and CHECKDB.

Improve this answer
3
  • I understand your point. I will see how can we fit a restore scenario into our DR plan, but basically, you're saying that I should keep my log backups until I'm 100% sure that I already have a valid backup that "contains" them, right?
    – ddaniel
    Commented Feb 29, 2016 at 22:27
  • Let me ask it this way: what's the maximum amount of data that you're okay with losing? If the answer is 0, always play it safe.
    – user541852587
    Commented Feb 29, 2016 at 22:46
  • 15 or 30 minutes of data-loss is acceptable, this is why I'm backing up the logs every 15 minutes. I'm thinking about keeping the logs for a week, and in the weekend automate a restore simulation. If that succeeds, I'll delete the logs. Thanks!
    – ddaniel
    Commented Mar 1, 2016 at 6:49
2

I would play it safe and delete files older than three days, a week, two weeks, what have you. You never know when management will run to you saying someone accidentally deleted data yesterday or before the weekend and they need to restore to a point in time. Make sure you retain enough data to cover all of those possible scenarios, and if you don't have enough space, buy more - it'll be much cheaper than being up the creek without a paddle, trust me.

Also, make sure you are testing the ability to restore your backups before deleting older ones, and validating they're consistent. You suggested that you would feel comfortable deleting yesterday's logs based on the fact that last night's backup executed successfully. That the backup executed successfully isn't enough. You could be backing up corruption for a week, and how would you know unless you actually tried to restore?

Improve this answer
1
  • I like the idea of being prepared for unexpected situations, when you might need to use the backups for requests other than just disaster recovery. I really appreciate the tip. I will mark user541852587's answer though, for including the required commands, and to encourage him as he's new here on dba :) Thank you.
    – ddaniel
    Commented Feb 29, 2016 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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