Planning Maintenance Plans

%3CLINGO-SUB%20id%3D%22lingo-sub-1209112%22%20slang%3D%22en-US%22%3EPlanning%20Maintenance%20Plans%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1209112%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20thinking%20a%20trans-log%20backup%20isn't%20needed%20if%20the%20DB%20is%20backing%20up%20fulls%20daily.%20Is%20that%20right%20in%20some%20cases%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1211210%22%20slang%3D%22en-US%22%3ERe%3A%20Planning%20Maintenance%20Plans%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1211210%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F512116%22%20target%3D%22_blank%22%3E%40TBrown2020%3C%2FA%3E!%3C%2FP%3E%3CP%3EWell%2C%20this%20is%20definitely%20an%20%22it%20depends%22%20answer%20since%20you%20don't%20specify%20the%20environment%20and%20the%20objective%20of%20the%20database%20but%20I'm%20willing%20to%20give%20it%20a%20try%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20it%20all%20depends%20on%20the%20recovery%20model%20of%20the%20database.%20If%20it's%20%3CSTRONG%3ESimple%3C%2FSTRONG%3E%20then%20probably%20you%20don't%20care%20much%20about%20the%20surviving%20a%20database%20failure%20since%20your%20last%20good%20full%20backup.%20In%20that%20case%20and%20%3CU%3E%3CSTRONG%3Eonly%3C%2FSTRONG%3E%3C%2FU%3E%20you%20can%20do%20away%20without%20a%20transaction%20log%20backup%2C%20in%20reality%20you%20can't%20even%20take%20a%20transaction%20log%20backup%20on%20that%20recovery%20model%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20case%20your%20database%20is%20either%20%3CSTRONG%3EFull%3C%2FSTRONG%3E%20or%20%3CSTRONG%3EBulk-logged%3C%2FSTRONG%3E%20then%20you%20should%20probably%20take%20backup%20i.e.%20once%20a%20day%2C%20to%20clean%20up%20the%20transactions%20that%20have%20been%20checkpointed%20and%20to%20keep%20the%20log%20from%20growing%20till%20the%20next%20big%20bang.%20This%20also%20depends%20on%20how%20many%20transactions%20the%20database%20has%20per%20day%20or%20to%20put%20it%20simpler%20how%20fast%20the%20log%20is%20filling%20up.%3CBR%20%2F%3E%3CBR%20%2F%3EOf%20course%2C%20it%20all%20depends%20if%20we%20are%20talking%20about%20a%20production%20database%20of%20a%20customer%20then%20it%20comes%20down%20to%20the%20SLA%20for%20RPO.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHTH%2C%3C%2FP%3E%3CP%3EVassilis%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Visitor

Good Morning,

 

I'm thinking a trans-log backup isn't needed if the DB is backing up fulls daily. Is that right in some cases?

1 Reply

Hi, @TBrown2020!

Well, this is definitely an "it depends" answer since you don't specify the environment and the objective of the database but I'm willing to give it a try :)

 

First of all, it all depends on the recovery model of the database. If it's Simple then probably you don't care much about the surviving a database failure since your last good full backup. In that case and only you can do away without a transaction log backup, in reality you can't even take a transaction log backup on that recovery model :)

 

In case your database is either Full or Bulk-logged then you should probably take backup i.e. once a day, to clean up the transactions that have been checkpointed and to keep the log from growing till the next big bang. This also depends on how many transactions the database has per day or to put it simpler how fast the log is filling up.

Of course, it all depends if we are talking about a production database of a customer then it comes down to the SLA for RPO.

 

HTH,

Vassilis