Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    38

    Unanswered: 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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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/we...&enterthread=y

    Are the columns in the index float datatypes?

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    What about an INDEXDEFRAG instead of a rebuild?

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    May 2006
    Posts
    38
    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.

  6. #6
    Join Date
    May 2006
    Posts
    38
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •