DB Maintenance Plan - Data Optimization?

When putting together a Maintenance Plan, after selecting the databases, the
topic is Data Optimization. Under this topic are options "Reorganize data
and index pages" and "Remove unused space from database files". Can both or
either of these be performed on a database that should be constantly
available to users?
Every time I've heard the word "rebuild" (as far as indexes) or "reclaiming
free space" it always seemed to indicate that the database would somehow
become temporarily unavailable to users during these processes. The only
warning I've seen about changing to single-user mode was during index
integrity checking if the option to try to repair a bad index was selected,
but I've read nothing about these two options.
Are these data optimization options safe for a "live" production database?
Thanks for any help.Don Miller wrote:
> When putting together a Maintenance Plan, after selecting the databases, the
> topic is Data Optimization. Under this topic are options "Reorganize data
> and index pages" and "Remove unused space from database files". Can both or
> either of these be performed on a database that should be constantly
> available to users?
> Every time I've heard the word "rebuild" (as far as indexes) or "reclaiming
> free space" it always seemed to indicate that the database would somehow
> become temporarily unavailable to users during these processes. The only
> warning I've seen about changing to single-user mode was during index
> integrity checking if the option to try to repair a bad index was selected,
> but I've read nothing about these two options.
> Are these data optimization options safe for a "live" production database?
> Thanks for any help.
>
>
Those processes are nothing more than a reindex operation followed by a
database shrink, either of which can be done on a live database. You
shouldn't shrink unless absolutely necessary though.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks again, BUT with the problems I've discussed with you in another
thread regarding a bloated database...
> You
> shouldn't shrink unless absolutely necessary though.
... I don't want to get back to a database that is ten times bigger than it
needs to be and would prefer the shrink process NOT to depend on manual
human intervention!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2F85D.9060405@.realsqlguy.com...
> Don Miller wrote:
> > When putting together a Maintenance Plan, after selecting the databases,
the
> > topic is Data Optimization. Under this topic are options "Reorganize
data
> > and index pages" and "Remove unused space from database files". Can both
or
> > either of these be performed on a database that should be constantly
> > available to users?
> >
> > Every time I've heard the word "rebuild" (as far as indexes) or
"reclaiming
> > free space" it always seemed to indicate that the database would somehow
> > become temporarily unavailable to users during these processes. The only
> > warning I've seen about changing to single-user mode was during index
> > integrity checking if the option to try to repair a bad index was
selected,
> > but I've read nothing about these two options.
> >
> > Are these data optimization options safe for a "live" production
database?
> >
> > Thanks for any help.
> >
> >
> >
> Those processes are nothing more than a reindex operation followed by a
> database shrink, either of which can be done on a live database. You
> shouldn't shrink unless absolutely necessary though.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Don Miller wrote:
> Thanks again, BUT with the problems I've discussed with you in another
> thread regarding a bloated database...
>> You
>> shouldn't shrink unless absolutely necessary though.
> ... I don't want to get back to a database that is ten times bigger than it
> needs to be and would prefer the shrink process NOT to depend on manual
> human intervention!
>
You will forever be chasing fragmentation issues if you automate a
database shrink. Determine an appropriate size for the database (and
log file), give it a reasonable growth allowance, and then schedule
periodic reindexes.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> You will forever be chasing fragmentation issues if you automate a database shrink.
Just to emphasize the point, I had a colleague who was surprised that the database (data part,
excluding log) only needed to be 4GB, of which vast majority was in one table. So he shrank, and
then rebuilt. The rebuild required 4GB free space (4GB table with clustered index). So immediately
after the shrink, the database jumped up to 8GB, with 4GB free space.
So what if he did rebuild and then shrink? Well, the shrink would more or less undo the rebuild,
again making both operations moot, more or less.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44F2FCC8.7030506@.realsqlguy.com...
> Don Miller wrote:
>> Thanks again, BUT with the problems I've discussed with you in another
>> thread regarding a bloated database...
>> You
>> shouldn't shrink unless absolutely necessary though.
>> ... I don't want to get back to a database that is ten times bigger than it
>> needs to be and would prefer the shrink process NOT to depend on manual
>> human intervention!
> You will forever be chasing fragmentation issues if you automate a database shrink. Determine an
> appropriate size for the database (and log file), give it a reasonable growth allowance, and then
> schedule periodic reindexes.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tibor Karaszi wrote:
>> You will forever be chasing fragmentation issues if you automate a
>> database shrink.
> Just to emphasize the point, I had a colleague who was surprised that
> the database (data part, excluding log) only needed to be 4GB, of which
> vast majority was in one table. So he shrank, and then rebuilt. The
> rebuild required 4GB free space (4GB table with clustered index). So
> immediately after the shrink, the database jumped up to 8GB, with 4GB
> free space.
> So what if he did rebuild and then shrink? Well, the shrink would more
> or less undo the rebuild, again making both operations moot, more or less.
>
Nice, the endless loop... This kind of stuff REALLY needs to be
explained better in the Maintenance Plan wizard. They make it seem SOOO
easy and carefree...
Tracy McKibben
MCDBA
http://www.realsqlguy.com