MS SQL Server - DBCC INDEXDEFRAG fills the log

Author

DBCC INDEXDEFRAG fills the log

Meher

2006-02-16, 9:23 am

Hi:

Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:

1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.

The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.

MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions are
highly appreciated.

Thanks

Andrew J. Kelly

2006-02-17, 7:23 am

Turning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP

"Meher" <Meher@discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37- BAFF05AD878A@microso
ft.com...
> Hi:
>
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
>
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
>
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
>
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
>
> Thanks

Sponsored Links