DBREINDEX and Transaction Log

I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>|||DBREINDEX is transaction protected. If it failed, the you had an internal rollback. This means that
you cannot resume the process. What did you reindex? What indexes does the table have? In general,
if you have a clustered index, you need same amount of free space in the database as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amount (roughly) of space
available for the transaction log (as well for each non-clustered index). One thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't transaction protected).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used more than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any changes to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage.
>|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> >I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log
> >space. Suddenly it needed more free space and the DBREINDEX failed.
> >
> > Is it somehow possible to resume the process? There hasn't been any
changes to the DB table in the
> > mean time. If I can't do that then how could I estimate the log file
usage.
> >
>