How do you handle this issue?

[bessebo]

We run a rebuild of indexes of our 50+ GB database every Sunday morning at 6:00AM. If we perform a transaction log backup after this rebuilding of indexes we end up with a HUGE transaction log backup. So, I am thinking that maybe the best thing to do would be to perform a BACKUP LOG with INIT right after the rebuilding of indexes job runs because then the transaction log backup would be cleaned out and we would now have a reasonable-sized transaction log backup as a result. Also, we perform a full backup of this database the previous night but, the issue could be, what if the full backup fails the night before and I have now gotten rid of our transaction log backup.

I am wondering if anyone out there has a similar issue with performing a tlog backup right after the rebuilding of indexes job runs. How and when do you fit the rebuilding of indexes into your backup strategy?

Thanks,
Bessebo

 

[ptheriault]

Re-Indexing is logged, but,If you are backing up your database log properly this really shouldn't be an issue. What is your backup schedule?

It should be something like this.

Daily Full backups (12 AM)
Daily incremental log backups (Starting after backup completes run a log backup every 30 min or so)
Daily Log Init (11:45 PM Init the log just before your full data backup)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bessebo]

Paul,
Given your scenario, what happens if your full backup fails and you have already run the backup log with init 15 minutes before that? Unless you have copied the transaction log backup off to tape prior to performing the backup log with init, then how will you recover should you have a catastrophic failure?
Wouldn't it make more sense to init the log after the full backup is successful and not to do so if it fails?

Thanks,
Bessebo

 

[ptheriault]

As part of my backups I copy the log files off the server. But you are correct and I had my times wrong. I run the init after the db backup but before I start the incrementals. Sorry about that.

- Paul
- If at first you don't succeed, find out if the loser gets anything.