Sql-server – Transaction log backups cleaning

backupmaintenance-planssql servertransaction-log

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.


Best Answer

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.