July 1, 2013 at 11:58 am
So, skimming blogs this fine day, I came across a suggestion to switch a DB from Full Recovery to Bulk-Logged when doing index rebuilds (especially LARGE indexes.) The stated benefit is that you don't lose the ability to recover to point-in-time (well, presumably except for the period when you're in Bulk-Logged)
MSDN certainly backs up the recommendation to go to Bulk-Logged for such things, but doesn't (including in the "Choosing a recovery model" topic) explicitly state that you won't lose PiTR.
So, is this true? Can you, say when you're planning an index rebuild, switch the DB in question from Full Recovery to Bulk-Logged, run your rebuild, then kick back to Full and still have a valid log chain? Hypothetically, say taking a log backup before you start (and before you change the recovery model,) then one when you're done, kick it back to Full, and the next log backup will still be OK?
Thanks,
Jason
July 1, 2013 at 12:04 pm
Well, this seems to back up what I found: http://www.pythian.com/blog/are-you-switching-to-bulk_logged-recovery-model-know-your-risks/[/url] although with the recommendation that you *HAVE* to take a TLog backup IMMEDIATELY after you finish the bulk operation and *BEFORE* you switch back to full recovery...
Plus I just noticed the title, obviously I can't spell...
😛
Jason
July 1, 2013 at 3:52 pm
http://www.sqlservercentral.com/articles/Recovery+Model/89664/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2013 at 5:24 pm
Cool, thanks!
Funny thing is, I think I read that article back when it came out...
I'm getting old, my memory ain't what it used to be...
😀
Seriously though, sounds like if I need to do index rebuilds on the heck table (that 1/2 billion {well, 1/3 of a billion} row table I recently ADDED a clustered index to) the best thing to do would be:
0. Wait for the weekend when there's no activity on the DB
1. Take a log backup
2. Switch DB to Bulk Logged
3. Run the index rebuild
4. Take a log backup
5. Switch back to full recovery
6. Accept the issue of not being able to restore to a point-in-time between step 2 and 5...
July 2, 2013 at 1:49 am
Yup.
The order of 1 and 2 and the order of 4 and 5 are irrelevant. Doesn't matter which recovery model you're in when the log backup runs, as long as there's one right before the minimally logged operation and one right after and as little user activity during the minimally logged operation as possible.
You lose the ability to do point in time restores during the log backup interval that contains the minimally logged operation, not during the time you're in bulk-logged recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply