October 29, 2008 at 10:16 am
Changing to Simple Mode for Reindexing
I have taken over DBA tasks at a company. I have some concerns about the timing of some jobs.
They are reindexing at 9pm. The job changes to simple mode, reindexes, changes back to full recovery.
They do a backup only once daily at 3 AM, backing up the database & transaction log.
Wouldn't the best bet to be at least the following:
Backup, Change to Simple, Reindex, Change to Full Recovery within the same job?
October 29, 2008 at 10:35 am
A few thoughts:
- The point of being in FULL recovery is to do point in time restores. If you DON'T want to do point in time restores, then why be in FULL mode at all.
- switching from FULL to SIMPLE means you've destroyed your ability to do point in time restores, since you've "broken the log chain". Once again - if point in time is important - switch to BULK instead, and run your reindexing in such a way as to leverage the minimal logging. then switch back and do an immediate log backup.
- It's usually advisable to perform multiple log backs during the day (again - so that you have something to go up against if you in fact need to do point in time restores). Not doing so greatly reduces the effectiveness, or rather increases the likelihood that you will not be able to get back to a specific point in time.
You should read up on the two articles in Books online about "switching recovery modes". In particular - look at the recommended steps about switching to and from Full/Bulk-logged modes.
Not knowing your background, you may also start by making sure you fully understand what recovery modes are and why you would use one over the other. The sequence of events that's happening would tend to tell me that your predecessor didn't understand the concepts at hand.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 29, 2008 at 3:27 pm
create and alter index are minimally logged under simple and bulk-logged. i would set the recovery to bulk logged for index rebuilds and back to full afterwards followed by a backup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 29, 2008 at 4:11 pm
Matt Miller (10/29/2008)
A few thoughts:- The point of being in FULL recovery is to do point in time restores. If you DON'T want to do point in time restores, then why be in FULL mode at all.
Couldn't agree more. If you're only backing up the tran log once per day, you're not leveraging full recovery model at all, so why bother with the switching. Just set it at simple and forget it.
If you would like better recoverability switch to full recovery, take regular tran log backups and you'll be fine. I'm guessing the reason for switching recovery models to begin with was because the log was filling up... that probably won't happen when you're doing regular log backups. esp. if you do one right before the reindexing starts.
The other poster's recommendations are good too (about switching to bulk logged). I would try my way first because it's less monkeying around.:hehe:
~Bot
Craig Outcalt
October 30, 2008 at 2:14 am
Matt Miller (10/29/2008)
Not knowing your background, you may also start by making sure you fully understand what recovery modes are and why you would use one over the other. The sequence of events that's happening would tend to tell me that your predecessor didn't understand the concepts at hand.
http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
Also, check out the headline article tomorrow. (http://www.sqlservercentral.com/articles/64582/)
Edit: Included link to aforementioned article, after publication
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
October 30, 2008 at 9:45 am
GilaMonster (10/30/2008)
Matt Miller (10/29/2008)
Not knowing your background, you may also start by making sure you fully understand what recovery modes are and why you would use one over the other. The sequence of events that's happening would tend to tell me that your predecessor didn't understand the concepts at hand.http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
Also, check out the headline article tomorrow.
Nice one! Good summary of the big "moving parts".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply