June 29, 2009 at 4:08 am
hi,
how can i create a index rebuild job. I have regular backups like full/trnlog backups(Daily 1 fullbackup,every 1 hour trnlog backup). how to plan for the rebuild index job.
🙂
June 29, 2009 at 4:24 am
Create a maintenance plan for index rebuild and a job will be created for that.Schedule it weekly once or once in two weeks depending on your database size and level of fragmentation.Schedule it in non-peak hours b'cos it affects the application.Track your disk size before and after the job ,it consumes high disk space for the transaction log.
June 29, 2009 at 4:32 am
hi thanks for your reply,
when we rebuild the index, the log size will be increased, so i'm planning to change the recovery model to simple/bulk logged. how it will affect the regular backups. please help me with the best plan for this situation.
🙂
June 29, 2009 at 4:45 am
No ,dont change the recovery model to simple,you cant perform transactional backups and you cannot recover the database point in time.
Keep it in FULL as it is now.Schedule the index rebuild job after the fullbackup so that u'll have the latest fullbackup for safer purpose.First do it on a development server with the same prod DB and you'll get an estimation of the disk space it uses and the time it takes.Based on this schedule the job on prod.
After the job runs the log file will be huge.But the hourly log backups will backup all the entries in the log and now your log file is huge but has free space.Next day schedule a shrink database job and the log file will come to normal.
So,keep your recovery plan as it is now and just schedule the rebuild job after fullbackup on one day of the week.
One more note:The log backups after the rebuild job will also be of huge sizes ,so plan for this disk space also.:-)
June 29, 2009 at 4:52 am
Switch it to bulk-logged. If you switch to simple you'll be breaking the log chain and will need to do a full backup after switching back to full.
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