Reorganize data and index pages option

We have a 40GB database and we, on a weekly basis, run a maintainance plan
with the option "Reorganize data and index pages" I think it is creating
a 20GB log file. The sql docs say that that option "Cause table indexes in
the database to be dropped and re-created with a new fill factor". I guess
that would explain it.
Is there a less expensive operation that can be performed? Like a DBCC
dbreindex but for all tables? I guess I could write a script to do that.
Any thoughts?
-alan>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables?
Same thing. See dbcc indexdefrag.
>--Original Message--
>We have a 40GB database and we, on a weekly basis, run a
maintainance plan
>with the option "Reorganize data and index pages" I
think it is creating
>a 20GB log file. The sql docs say that that
option "Cause table indexes in
>the database to be dropped and re-created with a new fill
factor". I guess
>that would explain it.
>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables? I guess I could write a
script to do that.
>Any thoughts?
>-alan
>
>.
>|||Sounds like you need the DBCC INDEXDEFRAG I put in SQL Server 2000. Look in
BOL for DBCC SHOWCONTIG and I included a script that will defrag all the
indexes in your database based on a fragmentation threshold you set.
Also see our new whitepaper on this topic at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan Berezin" <aberezin@.drillinginfo.com> wrote in message
news:eRunmZ7TDHA.2312@.TK2MSFTNGP12.phx.gbl...
> We have a 40GB database and we, on a weekly basis, run a maintainance plan
> with the option "Reorganize data and index pages" I think it is
creating
> a 20GB log file. The sql docs say that that option "Cause table indexes
in
> the database to be dropped and re-created with a new fill factor". I
guess
> that would explain it.
> Is there a less expensive operation that can be performed? Like a DBCC
> dbreindex but for all tables? I guess I could write a script to do that.
> Any thoughts?
> -alan
>