dbcc checkdb gets hung

I have a server running Windows 2003 with sql server 2000 8.00.973 hot fix
level that is hanging when I run dbcc checkdb on one particular database. It
keeps getting io and cpu time -- but just runs and run and runs.
Once when I looked at it there were 81 threads running for the DBCC's. It
was completely using up resources on the server. This is an 8 way processor.
We've let it run up to 11 hours before the server eventually had to be
rebooted.
The database it is hanging on is 250 Gb -- but I think it should still
finish in less than 11 hours. Usually we kill the process after 9 hours so
we can run our backups and let users start running their jobs again. We do
run this at night during our lowest usage.
Any suggestions?first check space in your tempdb with
dbcc checkdb (databasename) WITH ESTIMATEONLY
run CHECKDB when the system usage is low
and READ "DBCC CHECKDB Recommendations" i BOL
"DML" wrote:
> I have a server running Windows 2003 with sql server 2000 8.00.973 hot fix
> level that is hanging when I run dbcc checkdb on one particular database. It
> keeps getting io and cpu time -- but just runs and run and runs.
> Once when I looked at it there were 81 threads running for the DBCC's. It
> was completely using up resources on the server. This is an 8 way processor.
> We've let it run up to 11 hours before the server eventually had to be
> rebooted.
> The database it is hanging on is 250 Gb -- but I think it should still
> finish in less than 11 hours. Usually we kill the process after 9 hours so
> we can run our backups and let users start running their jobs again. We do
> run this at night during our lowest usage.
> Any suggestions?
>|||We do run CHECKDB at night during low usage, and run it in the same job as
backups so that they do not conflict. Disk backups are done in the morning
when we're sure our db backups are finished. Tempdb is on SAN space -- and
has over 2 Gb allocated to it with 10% growth set and more space available.
We run it with NO_INFOMSGS also. We run the same steps on our other 100
servers.
It just hangs in this 1 particular database. It is not the largest db we
have. This is also a new server we finished setting up in the last few
months and is one side of an Active Active cluster.
I ran the with estimate only and it only said I'd need around 2 Gb of tempdb
space.
Any other suggestions would be most helpful.
"Aleksandar Grbic" wrote:
> first check space in your tempdb with
> dbcc checkdb (databasename) WITH ESTIMATEONLY
> run CHECKDB when the system usage is low
>
> and READ "DBCC CHECKDB Recommendations" i BOL
>
> "DML" wrote:
> > I have a server running Windows 2003 with sql server 2000 8.00.973 hot fix
> > level that is hanging when I run dbcc checkdb on one particular database. It
> > keeps getting io and cpu time -- but just runs and run and runs.
> >
> > Once when I looked at it there were 81 threads running for the DBCC's. It
> > was completely using up resources on the server. This is an 8 way processor.
> >
> > We've let it run up to 11 hours before the server eventually had to be
> > rebooted.
> >
> > The database it is hanging on is 250 Gb -- but I think it should still
> > finish in less than 11 hours. Usually we kill the process after 9 hours so
> > we can run our backups and let users start running their jobs again. We do
> > run this at night during our lowest usage.
> >
> > Any suggestions?
> >
> >|||Let it finish. Some things to consider:
1) what are the disk queue lengths on the drives holding the database? (i.e
is your IO subsystem the bottleneck)
2) does it complete a lot faster if you use the NOINDEX option? (See BOL).
If so, you've probably got a corruption somewhere in a non-clustered index
which is triggering a much more expensive set of checks to find the exact
row with the corruption in. In which case, remove the NOINDEX option and let
it complete so you know where the corruption is.
Number 2 is my bet.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"DML" <DML@.discussions.microsoft.com> wrote in message
news:81778FD5-BEC9-4D52-8A71-14396E6F187F@.microsoft.com...
> We do run CHECKDB at night during low usage, and run it in the same job as
> backups so that they do not conflict. Disk backups are done in the
morning
> when we're sure our db backups are finished. Tempdb is on SAN space --
and
> has over 2 Gb allocated to it with 10% growth set and more space
available.
> We run it with NO_INFOMSGS also. We run the same steps on our other 100
> servers.
> It just hangs in this 1 particular database. It is not the largest db we
> have. This is also a new server we finished setting up in the last few
> months and is one side of an Active Active cluster.
> I ran the with estimate only and it only said I'd need around 2 Gb of
tempdb
> space.
> Any other suggestions would be most helpful.
> "Aleksandar Grbic" wrote:
> > first check space in your tempdb with
> > dbcc checkdb (databasename) WITH ESTIMATEONLY
> >
> > run CHECKDB when the system usage is low
> >
> >
> > and READ "DBCC CHECKDB Recommendations" i BOL
> >
> >
> > "DML" wrote:
> >
> > > I have a server running Windows 2003 with sql server 2000 8.00.973 hot
fix
> > > level that is hanging when I run dbcc checkdb on one particular
database. It
> > > keeps getting io and cpu time -- but just runs and run and runs.
> > >
> > > Once when I looked at it there were 81 threads running for the DBCC's.
It
> > > was completely using up resources on the server. This is an 8 way
processor.
> > >
> > > We've let it run up to 11 hours before the server eventually had to be
> > > rebooted.
> > >
> > > The database it is hanging on is 250 Gb -- but I think it should still
> > > finish in less than 11 hours. Usually we kill the process after 9
hours so
> > > we can run our backups and let users start running their jobs again.
We do
> > > run this at night during our lowest usage.
> > >
> > > Any suggestions?
> > >
> > >|||We were able to track down error messages regarding 2 tables in the database.
We ran DBCC Checktable against both tables. One table came back and the
other hung. On the table that hung (357 million rows) we dropped/recreated
the indexes and this seemed to fix the problem.
Thanks for your help.
"Paul S Randal [MS]" wrote:
> Let it finish. Some things to consider:
> 1) what are the disk queue lengths on the drives holding the database? (i.e
> is your IO subsystem the bottleneck)
> 2) does it complete a lot faster if you use the NOINDEX option? (See BOL).
> If so, you've probably got a corruption somewhere in a non-clustered index
> which is triggering a much more expensive set of checks to find the exact
> row with the corruption in. In which case, remove the NOINDEX option and let
> it complete so you know where the corruption is.
> Number 2 is my bet.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "DML" <DML@.discussions.microsoft.com> wrote in message
> news:81778FD5-BEC9-4D52-8A71-14396E6F187F@.microsoft.com...
> > We do run CHECKDB at night during low usage, and run it in the same job as
> > backups so that they do not conflict. Disk backups are done in the
> morning
> > when we're sure our db backups are finished. Tempdb is on SAN space --
> and
> > has over 2 Gb allocated to it with 10% growth set and more space
> available.
> > We run it with NO_INFOMSGS also. We run the same steps on our other 100
> > servers.
> >
> > It just hangs in this 1 particular database. It is not the largest db we
> > have. This is also a new server we finished setting up in the last few
> > months and is one side of an Active Active cluster.
> >
> > I ran the with estimate only and it only said I'd need around 2 Gb of
> tempdb
> > space.
> >
> > Any other suggestions would be most helpful.
> >
> > "Aleksandar Grbic" wrote:
> >
> > > first check space in your tempdb with
> > > dbcc checkdb (databasename) WITH ESTIMATEONLY
> > >
> > > run CHECKDB when the system usage is low
> > >
> > >
> > > and READ "DBCC CHECKDB Recommendations" i BOL
> > >
> > >
> > > "DML" wrote:
> > >
> > > > I have a server running Windows 2003 with sql server 2000 8.00.973 hot
> fix
> > > > level that is hanging when I run dbcc checkdb on one particular
> database. It
> > > > keeps getting io and cpu time -- but just runs and run and runs.
> > > >
> > > > Once when I looked at it there were 81 threads running for the DBCC's.
> It
> > > > was completely using up resources on the server. This is an 8 way
> processor.
> > > >
> > > > We've let it run up to 11 hours before the server eventually had to be
> > > > rebooted.
> > > >
> > > > The database it is hanging on is 250 Gb -- but I think it should still
> > > > finish in less than 11 hours. Usually we kill the process after 9
> hours so
> > > > we can run our backups and let users start running their jobs again.
> We do
> > > > run this at night during our lowest usage.
> > > >
> > > > Any suggestions?
> > > >
> > > >
>
>