Index Maintenance/Calculate Log Space Required

We're resigned ourselves to the fact that the best way to maintain
recoverability and still do a complete rebuild of the indexes in our
databases is to ensure that we have adequate log space available for this
operation. My question is, how can I determine what adequate log space is?
Do I just need enough space to hold all of the indexes? How do I figure that
out?
Thanks.Michelle,
Can you clarify why you have linked recoverability of the databases with the
reindexing of the databases. I'm wanting to verify that your not asking abo
ut availability of the databases instead of recoverablitity.
Thanks!
STurner|||Depending on the database, I plan for the maintenance window to defragment t
he indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup. T
his reduces the amount of l
og space needed and still maintains our recoverability.|||We had been changing the recovery model but then you still end up with a
window in which you have no recovery. Some of our backups can take a long
time. Thanks for the suggestion, though.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
> Depending on the database, I plan for the maintenance window to defragment
the indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup.
This reduces the amount of log space needed and still maintains our
recoverability.|||I link it because we had been changing the recovery model to minimize the
log space required of the process. However, we would then have a window
where we couldn't recover the database to a point in time because it breaks
the log chain. Even if you do a full backup after changing the recovery
model back to full, you have still broken the log chain should you need to
recover to a point in time BEFORE the full backup finished but AFTER you
changed the recovery model to bulk-logged.
I realize that the database is available with all recovery models and is
available while rebuilding the indexes (except locks are taken which could
prevent access to some objects during the process).
So, my question remains, how would one calculate the log space required to
rebuild all of the indexes in a given database?
Thanks!
Michelle
"STurner" <anonymous@.discussions.microsoft.com> wrote in message
news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
> Michelle,
> Can you clarify why you have linked recoverability of the databases with
the reindexing of the databases. I'm wanting to verify that your not asking
about availability of the databases instead of recoverablitity.
> Thanks!
> STurner|||We have the same issues with our log shipping environment.
For a clustered index it's 1.2 X the data size. But I
can't seem to remember what it is for a non-clustered
index.

>--Original Message--
>I link it because we had been changing the recovery model
to minimize the
>log space required of the process. However, we would then
have a window
>where we couldn't recover the database to a point in time
because it breaks
>the log chain. Even if you do a full backup after
changing the recovery
>model back to full, you have still broken the log chain
should you need to
>recover to a point in time BEFORE the full backup
finished but AFTER you
>changed the recovery model to bulk-logged.
>I realize that the database is available with all
recovery models and is
>available while rebuilding the indexes (except locks are
taken which could
>prevent access to some objects during the process).
>So, my question remains, how would one calculate the log
space required to
>rebuild all of the indexes in a given database?
>Thanks!
>Michelle
>"STurner" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
the databases with[vbcol=seagreen]
>the reindexing of the databases. I'm wanting to verify
that your not asking
>about availability of the databases instead of
recoverablitity.
>
>.
>|||One question that hasn't been asked yet is why are you rebuilding the
indexes in the first place?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"michelle" <michelle@.nospam.com> wrote in message
news:e7BE8hRPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> We had been changing the recovery model but then you still end up with a
> window in which you have no recovery. Some of our backups can take a long
> time. Thanks for the suggestion, though.
> "Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
> news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
defragment[vbcol=seagreen]
> the indexes with dbcc dbreindex and change the recovery model to bulk.
When
> the this is complete I change the model to full and do a complete backup.
> This reduces the amount of log space needed and still maintains our
> recoverability.
>