checktable with repair_rebuild

Hi! Could anyone tell me how much free space is needed to run DBCC
Checktable with repair_rebuild option. I have a table with more than 100
million record with size around 120 G and when I ran the command it failed
because of space issue. I only had around 50 G. of free space left on that
drive.
Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is there
any command that I can use to use Tempdb for the rebuild process instead of
using the database space?
I appreciate your answer.If your running 2000 then you can specify the ESTIMATE_ONLY option to see
how much space you need in tempdb. Check out BOL for more details.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> Hi! Could anyone tell me how much free space is needed to run DBCC
> Checktable with repair_rebuild option. I have a table with more than 100
> million record with size around 120 G and when I ran the command it failed
> because of space issue. I only had around 50 G. of free space left on that
> drive.
> Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
there
> any command that I can use to use Tempdb for the rebuild process instead
of
> using the database space?
> I appreciate your answer.
>
>|||However the ESTIMATEONLY option only works out how much space is required to
run the check - it does not know what repairs may be necessary and how much
space they will require. For an index rebuild, you will need the same amount
of free space as if you were running DBCC DBREINDEX on the same index.
BTW, if you have determined that there is an integrity problem, it is in
your best interestes to do root-cause analysis of the problem and see why it
happened (almost certainly hardware). Examine your event logs, the SQL
Server error logs and run any hardware diagnostics you can - the odds are it
will happen again.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> If your running 2000 then you can specify the ESTIMATE_ONLY option to see
> how much space you need in tempdb. Check out BOL for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > Hi! Could anyone tell me how much free space is needed to run DBCC
> > Checktable with repair_rebuild option. I have a table with more than 100
> > million record with size around 120 G and when I ran the command it
failed
> > because of space issue. I only had around 50 G. of free space left on
that
> > drive.
> > Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
> there
> > any command that I can use to use Tempdb for the rebuild process instead
> of
> > using the database space?
> >
> > I appreciate your answer.
> >
> >
> >
>|||Thanks for the answer.
In order to find what may have caused this, our Network guys did some online
hardware diognistic and didn't see any problems. Now we are planning to do
offline diognistic. I have one question on this:
Is there any specifc hardware (disk i/o, cpu, memory etc) do we need to
focus on? In other words what has been the main culprit from hardware side
causing database corruption, in your experience?
I appreciate your answer.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eYh248UJEHA.3436@.tk2msftngp13.phx.gbl...
> However the ESTIMATEONLY option only works out how much space is required
to
> run the check - it does not know what repairs may be necessary and how
much
> space they will require. For an index rebuild, you will need the same
amount
> of free space as if you were running DBCC DBREINDEX on the same index.
> BTW, if you have determined that there is an integrity problem, it is in
> your best interestes to do root-cause analysis of the problem and see why
it
> happened (almost certainly hardware). Examine your event logs, the SQL
> Server error logs and run any hardware diagnostics you can - the odds are
it
> will happen again.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> > If your running 2000 then you can specify the ESTIMATE_ONLY option to
see
> > how much space you need in tempdb. Check out BOL for more details.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > > Hi! Could anyone tell me how much free space is needed to run DBCC
> > > Checktable with repair_rebuild option. I have a table with more than
100
> > > million record with size around 120 G and when I ran the command it
> failed
> > > because of space issue. I only had around 50 G. of free space left on
> that
> > > drive.
> > > Does it require same free space as in DBCC DBReindex (1.2 * Table)? Is
> > there
> > > any command that I can use to use Tempdb for the rebuild process
instead
> > of
> > > using the database space?
> > >
> > > I appreciate your answer.
> > >
> > >
> > >
> >
> >
>|||In my experience, the major culprits have been bad drives, cables and
occasional problems with controllers. Make sure you're on the latest
software rev for all your controllers etc.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:uSAp727JEHA.2452@.TK2MSFTNGP09.phx.gbl...
> Thanks for the answer.
> In order to find what may have caused this, our Network guys did some
online
> hardware diognistic and didn't see any problems. Now we are planning to do
> offline diognistic. I have one question on this:
> Is there any specifc hardware (disk i/o, cpu, memory etc) do we need to
> focus on? In other words what has been the main culprit from hardware side
> causing database corruption, in your experience?
> I appreciate your answer.
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eYh248UJEHA.3436@.tk2msftngp13.phx.gbl...
> > However the ESTIMATEONLY option only works out how much space is
required
> to
> > run the check - it does not know what repairs may be necessary and how
> much
> > space they will require. For an index rebuild, you will need the same
> amount
> > of free space as if you were running DBCC DBREINDEX on the same index.
> >
> > BTW, if you have determined that there is an integrity problem, it is in
> > your best interestes to do root-cause analysis of the problem and see
why
> it
> > happened (almost certainly hardware). Examine your event logs, the SQL
> > Server error logs and run any hardware diagnostics you can - the odds
are
> it
> > will happen again.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:OAWZVyOJEHA.2508@.TK2MSFTNGP10.phx.gbl...
> > > If your running 2000 then you can specify the ESTIMATE_ONLY option to
> see
> > > how much space you need in tempdb. Check out BOL for more details.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "james" <kush@.brandes.com> wrote in message
> > > news:%23u9f%23UNJEHA.2604@.tk2msftngp13.phx.gbl...
> > > > Hi! Could anyone tell me how much free space is needed to run DBCC
> > > > Checktable with repair_rebuild option. I have a table with more than
> 100
> > > > million record with size around 120 G and when I ran the command it
> > failed
> > > > because of space issue. I only had around 50 G. of free space left
on
> > that
> > > > drive.
> > > > Does it require same free space as in DBCC DBReindex (1.2 * Table)?
Is
> > > there
> > > > any command that I can use to use Tempdb for the rebuild process
> instead
> > > of
> > > > using the database space?
> > > >
> > > > I appreciate your answer.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> In order to find what may have caused this, our Network guys did some
online
> hardware diognistic and didn't see any problems. Now we are planning to do
> offline diognistic.
--
Hi James,
You should also consider SQLIOStress tool in your battery of tests:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional