DBCC DBREINDEX fails for table

Hi,

I am facing a rather peculiar issue where I am getting a floating point exception error while rebuilding index for a particular table.

---
Error Number : 3628

Message :

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628: [Microsoft][ODBC SQL Server Driver][SQL Server]A floating point exception occurred in the user process. Current transaction is canceled.

[Microsoft][ODBC SQL Server
----
This seems to be a rare issue ( as acknowledged by microsoft ) and they seem to suggest that it happens with SQL Server 2000 SP3 . I migrated my database into an SQL Server 2000 SP4 and started the rebuild again .. .. But it still failed with the same error ..

Am just hoping the microsoft guys are wrong and many of you have actually faced this stuff before.. Please let me know.

Thanks in advance,
Ranjit.That's one I have not come accross. Have a look at this thread, and see if you can follow the same steps:

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=70&threadid=45658&enterthread=y

Are the columns in the index float datatypes?|||What about an INDEXDEFRAG instead of a rebuild?

Regards,

hmscott|||My bet is that it is a corrupt value for a floating point number... Not all possible values are valid floating point values. Some of them are non-sensical, so any attempt to even read them produces a runtime error.

A simple test would be to do a SELECT * to see if you can successfully retrieve all of the rows.

If that is the case, you'll need to fix the row before you can successfully build the index.

-PatP|||Thanks MCrowley/Scott/Pat for all your help.
But I am not done yet and would be back after some more research.. maybe to ask you guys again or to let you what I did to overcome my issue :)

Thanks once again,
Ranjit.|||Just an update on this one ..

Well this was due to corrupt float data in the tables ..to track the errant row ..we tried to export it into a file from where we could see it .. so we tried various methods of export ..

we could not export it into an xls/textfile, As the no of rows were large, ..
export thru bcp was possible but we could not correct the data as it was in native format .. import through BCP failed surprisingly long before it encountered the corrupt data rows .. ( maybe some mismatch in the data during bcp export )

But the select statement was the best solution in identifying the corrupt rows .. SQL failed to select on the corrupt data rows .. and we knew the error lay in those rows ..

Once identified, an update command was successfully executed on the corrupt rows and rectified ..

PS: all this was done on a test database .. we still have to sit down with the business team and make the changes ..

Just wanted to know what could be the best way out

1) change the data in corrupt rows and make sure their application inputs correct data
2) alter the column of the tables to accomodate the data ..

Please let me know.

And Thanks once again for the wonderful help I recieved during this issue.

Warm Regards,
Ranjit.|||One option is to simply "plug" the offending values. If you know that column plugh of the row associated with PK 'xyzzy' is bad, you can simply:UPDATE myTable
SET plugh = 0e0
WHERE 'xyzzy' = PK

Another approach is to make a copy, the basic idea is simple, but implementing it can be a bit difficult to explain. The short answer is to copy all of the completely readable rows in the original table to a scratch table, then copy the columns you can get from the probem row or rows substituting some acceptable value for the problem columns.

The exact mechanics of this process get complicated, due to space/time/other constraints. Feel free to ask for more help if you run into problems, because there are often easy fixes for otherwise unsolvable problems if you're willing to "think outside the box" a bit!

-PatP

DBCC CHECKDB Problem

I run the DBCC for a particular database in Query
Analyzer, and it takes 3 minutes. I take the same SQL
script and place it in a job to be scheduled in Enterprise
Manager, and it takes 1 hour. Why? Please help.
Is there anything else running when you run the job? DBCC CHECKDB takes out
locks on the objects it checks, so it might have to wait for locks to be
released by other connections.
Jacco Schalkwijk
SQL Server MVP
"Canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:555501c42d25$d09852e0$a601280a@.phx.gbl...
> I run the DBCC for a particular database in Query
> Analyzer, and it takes 3 minutes. I take the same SQL
> script and place it in a job to be scheduled in Enterprise
> Manager, and it takes 1 hour. Why? Please help.
|||There was nothing running. To isolate, I made a copy of
the database and renamed it. There was nothing hitting
the database when the DBCC was running during the two
different scenarios.

>--Original Message--
>Is there anything else running when you run the job? DBCC
CHECKDB takes out
>locks on the objects it checks, so it might have to wait
for locks to be
>released by other connections.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Canaries" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:555501c42d25$d09852e0$a601280a@.phx.gbl...
Enterprise
>
>.
>
|||Try adding SET NOCOUNT ON at the beginning of the job step.
Andrew J. Kelly SQL MVP
"Canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:555501c42d25$d09852e0$a601280a@.phx.gbl...
> I run the DBCC for a particular database in Query
> Analyzer, and it takes 3 minutes. I take the same SQL
> script and place it in a job to be scheduled in Enterprise
> Manager, and it takes 1 hour. Why? Please help.
|||I checked out the connection properties for Query Analyzer
and used the same settings for the job. It still takes
the long time.

>--Original Message--
>Try adding SET NOCOUNT ON at the beginning of the job
step.
>--
>Andrew J. Kelly SQL MVP
>
>"Canaries" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:555501c42d25$d09852e0$a601280a@.phx.gbl...
Enterprise
>
>.
>

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:
[vbcol=seagreen]
> 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:
|||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[vbcol=seagreen]
> space.
> Any other suggestions would be most helpful.
> "Aleksandar Grbic" wrote:
fix[vbcol=seagreen]
database. It[vbcol=seagreen]
It[vbcol=seagreen]
processor.[vbcol=seagreen]
hours so[vbcol=seagreen]
We do[vbcol=seagreen]
|||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...
> morning
> and
> available.
> tempdb
> fix
> database. It
> It
> processor.
> hours so
> We do
>
>

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?
> > > >
> > > >
>
>

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. I
t
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 process
or.
> 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 s
o
> we can run our backups and let users start running their jobs again. We d
o
> 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:
[vbcol=seagreen]
> 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:
>|||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[vbcol=seagreen]
> space.
> Any other suggestions would be most helpful.
> "Aleksandar Grbic" wrote:
>
fix[vbcol=seagreen]
database. It[vbcol=seagreen]
It[vbcol=seagreen]
processor.[vbcol=seagreen]
hours so[vbcol=seagreen]
We do[vbcol=seagreen]|||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 l
et
> 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...
> morning
> and
> available.
> tempdb
> fix
> database. It
> It
> processor.
> hours so
> We do
>
>