November 9, 2015 at 8:58 am
I have a database that is approx. 40GB in size. Its a third party application and the vendor wants the indexes rebuilt weekly. I setup the job and keep running out of log space while it runs. During the day the usage creates less then 20MB of log backups with our current log backup plan. The rebuild uses 25GB of drive space. I don't want to waste presenting more SAN space for this task, so I'm trying to determine best solution to avoid this.
Couple questions.
1.) Any way to calculate the amount of space required for the index rebuild? It was able to rebuild with 20GB, I presented 5 more because of no free space after log grew and now it takes the 25gb. If I present 5 more I'm afraid it will just eat that without really needing it.
2.) I split up the rebuild to do tables and then another job to do views to allow a log backup to occur in between to free up space. Still grows to max disk available as each one of the jobs fills the logs. Any other tricks to minimize growth of the logs?
Any thoughts appreciated.
November 9, 2015 at 9:24 am
Only 20 MB of log activity means that the database is close to unused. Thus, indexes don't need to be rebuilt.
Ola Hallengren has a database maintenance script that takes fragmentation levels into account and runs the appropriate action based on thresholds.
Low fragmentation or tiny index = no action
Medium fragmentation = index reorganize
High fragmentation = index rebuild
-- Gianluca Sartori
November 9, 2015 at 9:55 am
Agreed. But the issue is that the vendor is called because of performance issues and they claim the need to rebuild indexes. I argue against and then we rebuild to avoid the issue of pointing fingers. Hence I want to limit wasting resources (disk) on this further.
November 9, 2015 at 10:08 am
Try to take more frequent log backups during the rebuild process. You could take log backups every 1 minute and put a delay of 1 minute between each table rebuild.
-- Gianluca Sartori
November 9, 2015 at 2:18 pm
If possible
- switch recovery model of the database to simple before starting rebuild and switch to full once done ?
- Partitioning may also help where you do have option to rebuild one partition at a time.
November 17, 2015 at 7:47 pm
have2much (11/9/2015)
If possible- switch recovery model of the database to simple before starting rebuild and switch to full once done ?
- Partitioning may also help where you do have option to rebuild one partition at a time.
Ah... be careful now. If you switch to the SIMPLE recovery model, you're putting yourself in harms way, not to mention breaking the log file backup chain. If you do elect switching to SIMPLE (and I don't recommend it in most cases), you will need to at least take a DIF backup to re-establish the log chain.
In this case, a slightly better option would be to slip into the BULK LOGGED recover mode. Index REBUILDs are minimally logged in the BULK LOGGED model and, although it changes where you can do a point in time restore to, it doesn't break the log chain.
Just in case someone thinks of it, REORGANIZE is fully logged no matter the recovery model. Still, that can be an option if you increase the frequency of the log file backups to keep the logfile from growing and REORGANIZEs are done within the pages already allocated for whatever index you have. That's good for space limited disks where the MDF files live. Not so good for log files... back them up every 5 minutes or so during index reorgs on space limited disks.
Also, if you do REORGANIZE instead of REBUILD, you'll probably also have to update stats, which are one of the keys to the optimizer selecting good executions plans.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2015 at 7:52 pm
p.s. Allocating another 50-100GB to the app is going to be a whole lot cheaper than you having to mess around all the time whenever you defragment the tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply