July 18, 2008 at 2:36 pm
Question...Is it worth it to perform a full backup after I run my REORG operations?
My defrag process runs as such:
# DBCC CHECKDB WITH PHYSICAL_ONLY prior.
# Switch from FULL to BULK_LOGGED recovery. -- I have an alert system that helps manage logging during a reorg and REORG is a minimal-logged operation in the BULK_LOGGED model.
# Defrag database via REORGANIZE
# DBCC CHECKDB WITH PHYSICAL_ONLY post.
# Switch back to FULL recovery.
So, since I do a post DBCC check, and my recovery model switching isn't breaking my LSN chain, would any of you run full backups afterwords? The logs are all set, I have a consistent LSN chain - however, I now have some large individual t-log backups that occured during the operation. I don't think that it's going to be worth doing full backups just so that I've got an init'd t-log device and a shorter physical list of t-logs to restore if I need to.
Beuller?
Guess I'm off to test the recovery and having to read all those logs... 😉
Your friendly High-Tech Janitor... 🙂
July 18, 2008 at 3:04 pm
After Switching recovery model you *must* take Tlog Backup if you want to recover to any point in time. While the DB was in Bulk_logged you lost point in time recoverability.
* Noel
July 18, 2008 at 3:06 pm
You can switch between full and bulk_logged without breaking the chain...that's one of the advantages to having bulk_logged as an option (that you can switch back & forth for these types of operations and not affect your recovery chain).
Edit:
I see your point about point-in-time recovery in that Bulk_Logged doesn't support that as a recovery option (just re-read the details in BOL to be sure).
To that point - there are 1/4 hourly t-log backups automatically. After the job runs, sometime in the next fifteen minutes a log backup will be taken. However, after reading and digesting this, I think I'll at least add a call to take a log backup after I reset my recovery model back to full.
Thanks for pointing that out noel.
Your friendly High-Tech Janitor... 🙂
July 18, 2008 at 4:32 pm
I think that you will find that if you compare performing transaction log backups versus a truncate log, then set to full recovery and then a full database backup, the transaction log backup files will be larger than a full backup file and the run time for transaction log backups will be longer than the time to make a full backup.
SQL = Scarcely Qualifies as a Language
July 18, 2008 at 5:02 pm
Carl Federl (7/18/2008)
I think that you will find that if you compare performing transaction log backups versus a truncate log, then set to full recovery and then a full database backup, the transaction log backup files will be larger than a full backup file and the run time for transaction log backups will be longer than the time to make a full backup.
I have indeed seen that situation before. The choice was made by management to endure the extra large t-logs and restore operations. I tried to explain to them what a restore meant in that regard for thier recovery - fortunately I never had to restore with those logs. Indeed, putting the database into simple mode and issuing a truncate on another connection was much friendlier on the logging along with a full backup.
In my current situation, I have the opportunity for a re-write of the defrag process and our systems are not quite so un-wieldly when it comes to defragging. Sure, it's (logging) still close to a 10:1 storage overhead vs. a Rebuild or switching to simple recovery, but it is quite manageable on this system.
Your friendly High-Tech Janitor... 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply