I have been reviewing a number of posts on this subject and nothing I have seen has directly answered my question.

Nightly an on-line backup (include logs) is performed on our production 9.1 UDB database running on a Windows 2007 server.

Logs are archived to a seperate drive on the server. This drive is currently at 95% of capacity and there are over 3,000 log files in the archive directory. My question is -- which of these files can safely be deleted without compromising any potential restore or recovery.

I would keep the logs that are about a week old, and dump the rest. You have nightly backups, so all of the last week's backups would have to be corrupted to have a problem trying to restore without the logs.

Andy

[QUOTE=ARWinner]and dump the rest/QUOTE]
Let DB2 do the cleaning for you; DB2 created the file so actually it is a db2-object. Use "db2 prune" for this.
DB2 Universal Database

all depends on how far in time you would like to restore your db
if you plan to restore a db from a backup of 2 weeks old, keep all log files after this backup

I've been wondering the same thing.....

I'm running nightly online backups starting with a FULL on Sunday and INCREMENTAL DELTAs Monday-Saturday.

How many log files would you recommend holding on to? A weeks worth? And we're talking about the logs inside the ~/db2i1/NODE0000/SQL00001/SQLOGDIR directory, correct?

I have a backup script I wrote for my HADR cluster, so I think I would incorporate the deletion inside there, or utilize the PRUNE command.

What say the almighty DB2 forum?

Like Guy said, the number of logs you keep depends on how far back you may be required to go.

If your company requires you to keep backup images for 3 months, then you also need to keep the corresponding logs. The "include logs" option will only save the logs required to rollforward to a minimum point in time (end of online backup), but you need to have more logs than what's stored in the image if you need to rollforward to a different point in time.

I usually is not necessary to keep logs older than the last few backups. If one is keeping backups for three months, it is unlikely that you would need ot roll forward the logs to a point in between the backups. But you must make sure that all backups have the "include logs" option for onliine backups, becasue an online backup with the logs active during the backup cannot be restored.

If you're 100% sure that you won't be required to rollforward to a point in between the backups, then no reason to keep all the logs. Our strategy has always been to keep the logs for as long as we required to keep the images...You never know what you may be asked to do.

AS Marcus pointed out. As long as you include logs in your backup, you should be ok to smoke them.

This is all really good stuff.

So here's a scenario, and I want to make sure I'm getting this right:

Sunday: Full Online backup include logs
Active Log # 20
Action: This will backup up the whole database and include Active Log #20

Monday: Incremental Delta backup include logs
Active Log #25
Action: This will backup the newly modified pages since Sunday's successful backup AND include Active Log #25

Tuesday: Incremental Delta backup include logs
Active Log #34
Action: This will backup the newly modified pages since Monday's successful backup AND include Active Log #34

Observation:
If business rules dictate that I only need to keep the logs responsible for restoring at any time for the previous day to present then I would keep Monday's backup, logfiles #26-#33, and Tuesday's backup

If the rules say that I don't need to backup to a certain point in time, just back it up, I would only keep Tuesday's backup and no logfiles

Is this correct?

What's the easiest way to determine which was the active log when the backup took place?

I'll answer your last question first:

You can use "db2 list history backup all for db <db name>" and look at the "Earliest Log" and "Current Log" value. This identifies the log range you need in order to rollforward to a minimum point in time (end of backup). This log range is part of the backup image when logs are included.


I'm not too clear about your first question.. Let's say, today is Wednesday and you have to restore your db back to Tuesday. Let's say, tuesday's backup completed at 2:30am. What is the requirement? To restore your db back to Tuesday 2:30am or some later time?

If the requirement is to restore it back to 2:30am (end of backup), then you only need to have the logs that were active while the backup was executing (those logs are part of the image). If you have to restore it back to 10am (for example), then you have to have additional logs.

If your Tuesday backup is good. And you crash on Wed before the back up. Technically speaking all you would need are the logs starting after your Tuesday backup. #35+. as #34- and before will be extracted from the back up image.

ok great, I think I'm getting it...

So needless to say, if I'm backing up every night, I only need to keep that day's logs around priot to the next backup....


I know this is really easy to grasp, I don't know why I'm having such a hard time with it...

OH! And is it safe to say that the logs you get rid of on the PRIMARY server, you can get rid of the same logs on the Standby server, in a clustered environment?

bulump, As long as you only want the ability to restore to some point in time on the previous day, all you need is:

1) Last Full Backup
2) All Delta Incremental backups since the Full backup and before the restore time.
3) All Logs since the last Delta Incremental backup before the restore time and up to and including the restore time.

For example, you might have the following timeline:

Sun 2:30 AM Full Backup
Logs
Mon 2:30 AM Delta Incremental Backup (1)
Logs
Tue 2:30 AM Delta Incremental Backup (2)
Logs
Wed 2:30 Am Delta Incremental Backup (3)
Logs

On Wed, you determine the need to restore to Tue 3:00 PM. You will need Full backup from Sun, Delta Incremental backup (1) from Mon, Delta Incremental Backup (2) from Tue, and Tue Logs.

If you never want to restore any earlier than the previous day, you could delete the Sun Logs on Tues and the Mon Logs on Wed, etc.