August 10, 2006 at 12:25 pm
One of my customers has a database of around 80GB. They have the standard optimization job set up in their maintenance plan, and it regularly fails because while running the trn log runs out of space, then it rolls back reporting failure. The database is set to autogrow, unrestricted. Drive space is a non-issue. The database file is a single file group, and the only resolution so far that I've seen online is to add further filegroups, but my company's application doesn't support this yet (or it hasn't been tested).
What other options are there for optimizing the indexes for large databases on a primary file group?
August 10, 2006 at 2:31 pm
You mention the transaction log becoming full. Does it grow in small enough increments. Is the initial size large enough to prevent shrinking/growing.
During the optimization job, is the database mode simple or has it more frequent log backups scheduled to compensate the logwriting of DBCC REINDEX?
August 10, 2006 at 4:03 pm
The database stays in full mode. The optimization job is out of the maintenance plan wizard so there's no extra configuration in it. It's unknown how large the initial size of the log was, but it was observed to be quite larger when the job was running, and after it failed, it was noticeably smaller by many GB. Can a log backup occur when an optimization is running?
I'll admit I'm not well versed in best practices for optimization jobs, and I know a lot of folks here don't recommend using the wizards.
August 11, 2006 at 12:53 am
The optimalisation job is problably DBCC REINDEX (hold locks)/DBCC INDEXDEFRAG(online) (keeping indexes in shape, fills log quite nicely) en perhaps sp_updatestats (for a better query plan).
Log backups can be done as the database isn't damaged,there are available connections and the database mode isn't simple.
August 11, 2006 at 8:34 am
Jo is correct that the optimization job uses DBCC REINDEX. Since your database is so large, the process probably takes hours to complete.
I have noticed a large transaction log file after an optimization job has completed. So, for large databases, I change the recovery mode to SIMPLE (using a job) before the optimization job and change it back to FULL after the optimization job.
After a maintenance plan generates the optimization job, you can add steps to it. But it not highly recommended since certain changes to the maintenance plan can cause the optimization job to be deleted. Then you would have to re-apply your changes once the job is generated again.
August 11, 2006 at 11:29 am
Ok, great. That info gives me some things to try out. Thanks for all the feedback. Barbara, when you change the mode are you doing that manually?
August 15, 2006 at 10:56 am
you should be aware that a re-index may be very intensive - especially on a slow disk subsystem and/or with std edition. To be honest 80Gb is pretty small for a database, but that said the dbcc may well grow the log to several GB. If you check BOL there is a routine to apply selective index rebuilds - this can be modified to delay etc. thus spreading the impact.
If you have the ability setting the database to simple for maint is good - but you should backup first and then after you have put it back into full you must backup again otherwise your tran logs will be invalid.
The other boring option is to sit and watch what it does - that way you'll be sure.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 17, 2006 at 8:31 am
I've run into some space issues with the dbcc reindex in the past. I had to write a custom proc for one db that after certain of the bigger tables I would backup the transaction log to free up the unused space. Otherwise the dbcc would just continue to grow the tlog until failure.
Tom
August 21, 2006 at 6:24 pm
Are you really sure that space is not a problem? The maintenance plan can take a lot of space when doing disk backups and reindexing - it's not smart about this. For an 80 GB db, expect to need at least an extra 160 GB free. If there are more dbs in the plan, expect to need more.
You might also want to check that the autogrow is set to a reasonable amount. I have had problems with leaving it set to a percentage. This was the case in tempdb on several of our servers. I set mine to grow by 25 MB. Perhaps the wait for the growth is too long? You can also enlarge the tran log in advance. Turn off auto shrink.
If most of your data is in one table, the tran log can almost double the size of your database. The trn log backup will be large.
Also, make sure it's not tempdb that has the problem. In some cases, tempdb is also used when doing a reindex - for sorting. The recovery model for tempdb should be simple.
There or other ways around this by avoiding the default maintenance plan. Search scripts for "reindex" on this site. I think there are scripts there that can re-index an index at time rather than everything at once.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply