DBCC INDEXDEFRAG and Transaction Log

Hi,
Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000 Enterprise
with SP3. I run DBCC INDEXDEFRAG on a table with only one primary key. The
size of the table is about 120MB, the size of the index is about 1.6MB.
Before running DBCC INDEXDEFRAG, I run DBCC SHOWCONTIG and found the Logical
Scan Fragmentation was about 10%. The problem is that the transaction log I
backup after running DBCC INDEXDEFRAG on this table is about 600MB. I did
backup transaction log before this operation. There is no other activities
on this server. And I checked there wasn't any open transaction. I don't
know why DBCC INDEXDEFRAG on a small index created so big transaction log.
I appreciate any information and help !
Thanks!
BillBill Wang wrote:
> Hi,
> Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> primary key. The size of the table is about 120MB, the size of the
> index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> The problem is that the transaction log I backup after running DBCC
> INDEXDEFRAG on this table is about 600MB. I did backup transaction
> log before this operation. There is no other activities on this
> server. And I checked there wasn't any open transaction. I don't
> know why DBCC INDEXDEFRAG on a small index created so big
> transaction log.
> I appreciate any information and help !
> Thanks!
>
> Bill
A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
you are running SP3.
http://support.microsoft.com/kb/q282286/
THe following article may explain some of the looging requirements. See
the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
section.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, David. It's SP3. I read both of articles before this operation. I will
try on another table and comfirm this problem.
Thanks for oyur help!
Bill
"David Gugick" wrote:
> Bill Wang wrote:
> > Hi,
> > Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> > Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> > primary key. The size of the table is about 120MB, the size of the
> > index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> > SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> > The problem is that the transaction log I backup after running DBCC
> > INDEXDEFRAG on this table is about 600MB. I did backup transaction
> > log before this operation. There is no other activities on this
> > server. And I checked there wasn't any open transaction. I don't
> > know why DBCC INDEXDEFRAG on a small index created so big
> > transaction log.
> >
> > I appreciate any information and help !
> >
> > Thanks!
> >
> >
> > Bill
> A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
> you are running SP3.
> http://support.microsoft.com/kb/q282286/
> THe following article may explain some of the looging requirements. See
> the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
> section.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>