Maint Plan Reindex LARGE log

Hello. Running SQL 2000, have a database in BULK_LOGGED
mode while a DBREINDEX Maint Plan is running, then back to
FULL recovery mode. Here's an example timeline...
-- In FULL Recovery Mode
10:00pm Trans Log Backup
10:15pm ALTER DATABASE to BULK_LOGGED recovery mode
10:30pm DB Maint Plan job to DB Optimize is run
10:45pm ALTER DATABASE to FULL recovery mode
11:00pm Trans Log Backup
questions...
Why does that 11pm Trans Log Backup file size almost equal
the full DATA file size? If in BULK_LOGGED I thought the
point was to minimally log during a reindex?!?
Why does the SQL 2000 Maint plan generate a DBREINDEX
statement with the SORTED_DATA_REORG parameter if that is
no longer supported in SQL 2000?
dbcc dbreindex(N'[dbo].[publishers]', N'', 0,
sorted_data_reorg)
Any ideas on how to do a reindex in SQL 2000, in
BULK_LOGGED mode, so the Trans Log backup file isn't as
large as the data files' THanks, BRuceBruce,
In BULK:LOGGED mode, all pages (might be extents, not sure) that were
modified between two log backup intervals are actually included in the log
backup. So the log backup will contain both log records as well as data
pages! If you reindex the entire database, then the entire database need to
be included in the log backup. You might want to use DBCC INDEXDEFRAG
instead of DBREINDEX, which can generate fewer log records.
> Why does that 11pm Trans Log Backup file size almost equal
> the full DATA file size? If in BULK_LOGGED I thought the
> point was to minimally log during a reindex?!?
Most probably just an oversight by the person who write the maint wizard.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:085b01c3faeb$c49455a0$a101280a@.phx.gbl...
> Hello. Running SQL 2000, have a database in BULK_LOGGED
> mode while a DBREINDEX Maint Plan is running, then back to
> FULL recovery mode. Here's an example timeline...
> -- In FULL Recovery Mode
> 10:00pm Trans Log Backup
> 10:15pm ALTER DATABASE to BULK_LOGGED recovery mode
> 10:30pm DB Maint Plan job to DB Optimize is run
> 10:45pm ALTER DATABASE to FULL recovery mode
> 11:00pm Trans Log Backup
> questions...
> Why does that 11pm Trans Log Backup file size almost equal
> the full DATA file size? If in BULK_LOGGED I thought the
> point was to minimally log during a reindex?!?
> Why does the SQL 2000 Maint plan generate a DBREINDEX
> statement with the SORTED_DATA_REORG parameter if that is
> no longer supported in SQL 2000?
> dbcc dbreindex(N'[dbo].[publishers]', N'', 0,
> sorted_data_reorg)
>
> Any ideas on how to do a reindex in SQL 2000, in
> BULK_LOGGED mode, so the Trans Log backup file isn't as
> large as the data files' THanks, BRuce|||Thanks Tibor. I was hoping BULK_LOGGED would help with
the size of the Trans Log backups when we use the DB Maint
Plans for reindexing. We'll try other options like you
suggested. THanks, Bruce
>--Original Message--
>Bruce,
>In BULK:LOGGED mode, all pages (might be extents, not
sure) that were
>modified between two log backup intervals are actually
included in the log
>backup. So the log backup will contain both log records
as well as data
>pages! If you reindex the entire database, then the
entire database need to
>be included in the log backup. You might want to use DBCC
INDEXDEFRAG
>instead of DBREINDEX, which can generate fewer log
records.
>
>> Why does that 11pm Trans Log Backup file size almost
equal
>> the full DATA file size? If in BULK_LOGGED I thought
the
>> point was to minimally log during a reindex?!?
>Most probably just an oversight by the person who write
the maint wizard.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:085b01c3faeb$c49455a0$a101280a@.phx.gbl...
>> Hello. Running SQL 2000, have a database in BULK_LOGGED
>> mode while a DBREINDEX Maint Plan is running, then back
to
>> FULL recovery mode. Here's an example timeline...
>> -- In FULL Recovery Mode
>> 10:00pm Trans Log Backup
>> 10:15pm ALTER DATABASE to BULK_LOGGED recovery mode
>> 10:30pm DB Maint Plan job to DB Optimize is run
>> 10:45pm ALTER DATABASE to FULL recovery mode
>> 11:00pm Trans Log Backup
>> questions...
>> Why does that 11pm Trans Log Backup file size almost
equal
>> the full DATA file size? If in BULK_LOGGED I thought
the
>> point was to minimally log during a reindex?!?
>> Why does the SQL 2000 Maint plan generate a DBREINDEX
>> statement with the SORTED_DATA_REORG parameter if that
is
>> no longer supported in SQL 2000?
>> dbcc dbreindex(N'[dbo].[publishers]', N'', 0,
>> sorted_data_reorg)
>>
>> Any ideas on how to do a reindex in SQL 2000, in
>> BULK_LOGGED mode, so the Trans Log backup file isn't as
>> large as the data files' THanks, BRuce
>
>.
>