Maintenance Plan - Optimizations

I ran a Optimization plan on Sunday morning and my database grew from 14GB
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully. Seriously, you might consider doing full backups right after
a reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."

>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backup
s
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/Real...fraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com