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 ro
llback. This means that
you cannot resume the process. What did you reindex? What indexes does the t
able have? In general,
if you have a clustered index, you need same amount of free space in the dat
abase as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amou
nt (roughly) of space
available for the transaction log (as well for each non-clustered index). On
e 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 t
ransaction 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...[vbc
ol=seagreen]
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used mo
re 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 change
s to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage
.
>[/vbcol]|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/pr...n/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...
more than 5 GB of Log[vbcol=seagreen]
changes to the DB table in the[vbcol=seagreen]
usage.[vbcol=seagreen]
>