Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2007-05-14 : 05:26:18
|
| If I keep my Complete Full Backups for 3 days, and my Tran Log Backups for 3 days, how should I time it so that trns files are deleted WITH their associated baks.So for example, I have...140507_1000_log.trn ---- fine140507_0930_log.trn ---- fine140507_0900_log.trn ---- fine140507_0830.bak ---- fine130507_1000_log.trn ---- fine130507_0930_log.trn ---- fine130507_0900_log.trn ---- fine130507_0830.bak ---- fine120507_1000_log.trn ---- Not needed120507_0930_log.trn ---- Not needed120507_0900_log.trn ---- Not neededI sometimes have the situation where the baks have been deleted before the trns. Whilst this is not a problem, there can be a lot trns hanging about before they are deleted. It's clearly a matter of timing. Where is it going wrong?Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2007-05-14 : 05:33:53
|
| Normally you set different timing to remove full backup and trn backup, the most thing you want to keep is the trn since its more important then your full backup. Normally, trn backup files is much smaller, so it shouldn't take up that much space right? |
 |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-14 : 05:45:21
|
| Yes, that's right.The timings are different, but they are not timed optimally so that they are deleted when the baks are. The trn files on the 120507 are redundant as they no longer have their corresponding bak file. So there is no need for them. Whilst they are small in size, if there is one occuring every 10 minutes, that's alot of trn files. I'd like to tidy it up, but no matter how much I jiggle the times around, I still end up getting left over trns files.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-14 : 08:27:09
|
| We do it the other way about.We store all the "backup filenames" in a database table, and have dependency between Full and Diff, and Diff and Tlog.We flag files as "Ready for delete", but they are not allowed to be deleted if Child Records exist.So if the Retention for a Full backup is 3 days, and they are taken, say, once a day, and the TLog backups are hourly, then a Full backup would be not deleted until all its TLog backups have been deleted, which would be after 3 days and 23 hours.This is needed, I reckon, to prevent deletion of a Tlog backup that is 3 days old but there are Tlog backups 3 days and 23 hours old, which cannot be restored unless the 3-day-old Tlog AND the 3-day-old Full are available.If they are all also on Tape then this may not be so important, just delete anything older than 3 days!We allow variation on Retention - so, for example:We take full backups once a week - retention 4 weeksDiff backups daily - retention 2 weeksTLog backups every 10 minutes, retention 3 daysSo the youngest Tlog backup we retain is 3 days, the oldest is 3 days 23 hours and 50 minutes, and so on.We allow override of the Retention period when we make a backup so, for example, just before rolling out a major update we may take a full backup with a longer retention period, so it hangs around until "all danger is passed", the Diffs and TLogs based on it will have long since been deleted before it is.Kristen |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-14 : 08:36:58
|
| Right.It's bloody confusing.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
|
|
|
|
|
Timing your Tran Log Backups