DB Optimatizing problem

Hi,
I found a problem (or issue) on SQL 2000. When I run the 'reorganize data
and index pages' (default value from Database Maintenance Plan Wizard) for
feq. update and 10G db. The job will use almost 10G transaction log to
complete. But in SQL7, only few hundred mb will be used.
Is it SQL 2000 behavior? How can I limit the transaction log space use?
Thanks & Regards,
Henry
Hi,
What is the recovery model set for the database. Check if the recovery model
is "FULL", FULL recovery model will log every action. So i recommend you to
change the recovery model to SIMPLE before doing this activity.
Note:
If you change the recovery model, you need to create the backup chain once
again by creating a FULL database backup
Thanks
Hari
MCDBA
"Henry" <remove_henrychoi@.mail.hongkong.com> wrote in message
news:OmVOl4#OEHA.628@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I found a problem (or issue) on SQL 2000. When I run the 'reorganize data
> and index pages' (default value from Database Maintenance Plan Wizard) for
> feq. update and 10G db. The job will use almost 10G transaction log to
> complete. But in SQL7, only few hundred mb will be used.
> Is it SQL 2000 behavior? How can I limit the transaction log space use?
> Thanks & Regards,
> Henry
>
|||The Maintenance Plan uses DBCC DBREINDEX which is fully
logged in SQL Server 2000. It runs under one transaction so
the log will not be truncated until it's done, even if you
are using simple recovery model. If you don't have enough
space for the logs to handle this operation, you can write
your own reindexing. Some approaches would be to do groups
of tables in batches and then backup the log in between the
batches. Or if you use DBCC INDEXDEFRAG, where it's not run
as one transaction and the log can be backed up during the
process.
You may not need to be reindexing all the tables in your
databases. And which method to use depends on other factors.
You really should check the following article for more
information:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
-Sue
On Mon, 17 May 2004 17:05:22 +0800, "Henry"
<remove_henrychoi@.mail.hongkong.com> wrote:

>Hi,
>I found a problem (or issue) on SQL 2000. When I run the 'reorganize data
>and index pages' (default value from Database Maintenance Plan Wizard) for
>feq. update and 10G db. The job will use almost 10G transaction log to
>complete. But in SQL7, only few hundred mb will be used.
>Is it SQL 2000 behavior? How can I limit the transaction log space use?
>Thanks & Regards,
>Henry
>