dbcc indexdefrag

Does dbcc indexdefrag cause transaction log growth? I've got a bit database
,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardles
s
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:

> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||In addition to the other posts:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.