repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
> > We have a large-ish database (160GB) which grew to over 300GB last
weekend
> > when the maintenance job failed. Half of it is unused but I can't shrink
it
> > due to index errors and when I run checkdb it also reports index errors.
I
> > would like to run checkdb with repair_rebuild but there is not much room
for
> > the log file to grow now the DB has gotten so big - it will max out
around
> > 8-10GB.
> >
> > My questions are:
> >
> > Will repair_rebuild need the log file to grow any larger than 8GB on a
160GB
> > database? If so can I just move the log file to a drive with more room?
> >
> > I can't copy the DB to another server and test out the repair_rebuild -
does
> > anyone have an idea how long it will take on a 160GB database? Are we
talking
> > several hours or days?
> >
> > Is DBCC reindex a better option in my case?
> >
> > Thanks