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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Timing your Tran Log Backups

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 ---- fine
140507_0930_log.trn ---- fine
140507_0900_log.trn ---- fine
140507_0830.bak ---- fine

130507_1000_log.trn ---- fine
130507_0930_log.trn ---- fine
130507_0900_log.trn ---- fine
130507_0830.bak ---- fine

120507_1000_log.trn ---- Not needed
120507_0930_log.trn ---- Not needed
120507_0900_log.trn ---- Not needed

I 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?
Go to Top of Page

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."
Go to Top of Page

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 weeks
Diff backups daily - retention 2 weeks
TLog backups every 10 minutes, retention 3 days

So 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
Go to Top of Page

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."
Go to Top of Page
   

- Advertisement -