Delete Old Transaction Log files?

Question

Delete Old Transaction Log files?

SQlLogic

SSCrazy Eights

Points: 9686
More actions
December 12, 2011 at 8:26 am

#391105

Hi Guys,
Like to know Best practices for Transaction Log you used for your enviornment.
Shrink T- log file or Truncate T-log file ?
Is it good to delete old transaction Log backup after having latest Full Backup and Differential Backup?
we Have full backup -weekly sun
Diff Backup - Mon to sat Every night
Transaction log - Every day ,every hour 9 am to 10 pm.
Any Suggestion please?
Thank you And have a good day
Happy Holidays in Advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Personally I like keeping log backups that cover 2 full backups. That way, if there's anything wrong with the latest, it's possible to go back to the oldest.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NJ-DBA SSChampion Points: 13834 More actions · Answer 2

logicinside22 (12/12/2011)
Hi Guys,
Like to know Best practices for Transaction Log you used for your enviornment.
Is it good to delete old transaction Log backup after having latest Full Backup and Differential Backup?
we Have full backup -weekly sun
Diff Backup - Mon to sat Every night
Transaction log - Every day ,every hour 9 am to 10 pm.
Any Suggestion please?
Thank you And have a good day
Happy Holidays in Advance

Basically my rule is "I will keep TLog backups as long as you tell me you need point in time recoverability". So if on a Wednesday, a data owner could say, "Please restore the database to 12pm on Monday", then you need to keep the TLOG backups all week long. If the data owners understand that we are only providing point in time recoverability for the current day, then I would say it is ok to discard tlog backups after each sucessful diff.

salum SSCommitted Points: 1598 More actions · Answer 3

General Best Practices for backup retention.

http://www.mssqltips.com/sqlservertip/1401/sql-server-database-backup-retention-periods/

java[/url]

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

I tend to follow Gail's advice. 2 Full backups + all logs.

Going back more than a couple days is almost always impractical for any system. For auditing purposes, I'd rather have less than more if I am not required to have the data.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

I would agree with what others have already said.

To give my 2 cents. We have a set of backups (Full,Diff and Log) since the last full backup on the server locally.

We do have archiving of the backup files in tapes for the last 4 weeks...

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 6

brendan woulfe

Hall of Fame

Points: 3888

December 12, 2011 at 12:22 pm

#1420212

Same as above. I usually keep a couple days worth.

Pam Brisjar SSChampion Points: 12094 More actions · Answer 7

I'm the exception to the rule. We have processes that run monthly, quarterly and annually. We also have some mandated auditing. So we keep roughly one month of backup data on site and shove off quite a bit to tape (high threshold is not determined by me / SQL Server DB needs). I have had cases where we have had to restore some rather old systems (max was I believe 3 months) but never that old to Prod - generally to a secondary server so data can be queried and potentially recovered.

But in general, much past a day or two tends to become irrelevant. All depends upon business needs and your resources.