Post reply

Help! Index corrupting? DBCC CheckTable failing?

  • December 2, 2003 at 5:26 pm

    #59584

    We are having quite a time since moving a large database to a new server (actually built new server, renamed as old to make seamless for users, etc.)

    Import 104 million row database (5 column) into table (CD_Assets_bad2) from existing (CD_Assets):

    Account(varchar(8))

    TransactionDate(datetime(8)

    Flow(varchar(1))

    Category(varchar(7))

    TotalValue(decimal(8))

    Run DBCC CheckTable - no issues.

    Create 4 non clustered indexes (3 single column, 1 two-column). All indexes create fine.

    Run DBCC CheckTable again and receive the following:

    Server: Msg 8951, Level 16, State 1, Line 1

    Table error: Table 'CD_Assets_bad2' (ID 244195920). Missing or invalid key in index 'idx_totalvalue' (ID 7) for the row:

    Server: Msg 8955, Level 16, State 1, Line 1

    Data row (1:11154499:98) identified by (RID = (1:11154499:98) ) has index values (TotalValue = -10).

    Server: Msg 8952, Level 16, State 1, Line 1

    Table error: Database 'CD', index 'CD_Assets_bad2.idx_totalvalue' (ID 244195920) (index ID 7). Extra or invalid key for the keys:

    Server: Msg 8956, Level 16, State 1, Line 1

    Index row (1:20855652:338) with values (TotalValue = -0¤

    4) points to the data row identified by (RID = (1:11154499:98)).

    DBCC results for 'CD_Assets_bad2'.

    There are 104397173 rows in 677904 pages for object 'CD_Assets_bad2'.

    CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'CD_Assets_bad2' (object ID 244195920).

    repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (CD.dbo.CD_Assets_bad2 ).

    Any ideas? It seems like some sort of corruption, but the index creates fine. If anyone can help please let me know. If I can provide any addtional information that might help, please let me know.

    Thanks,

    David

    david.schwartz@schwab.com

  • December 3, 2003 at 4:48 am

    #484428

    Depedning on how long it would take I would consider dropping the index and rebuilding it.

  • December 3, 2003 at 4:15 pm

    #484547

    We've tried that several times. We've dropped all indexes and run CheckTable again and gotten all clear. Create the indexes (each creating successfully) and then get errors similar to above

  • December 4, 2003 at 2:01 am

    #484560

    Again, depending on how much time (and disk space), you could bcp the data out in native format, drop the table, recreate the table, reload the data and rebuild the indexes

  • December 4, 2003 at 6:48 pm

    #484736

    Has any of the table definition(s) changed?

    what are the four indexes?

  • December 4, 2003 at 8:02 pm

    #484741

    There is a single column non-clustered index on Account, TransactionDate and TotalValue and a two-column non-clustered index on Account and TotalValue. The table defs have not changed.

  • December 8, 2003 at 2:39 am

    #485046

    Just an idea.

    Try to make one of your index clustered.

    That one will rearange your table and recreate all your existing nonclustered indexes.

    If you really have a table or index corruption it should shown up.

    Bye

    Gabor



    Bye
    Gabor

  • December 8, 2003 at 3:24 am

    #485050

    ARe you moving from SQL 7.0 to 2000? If so, the following KB article may apply: 298806 "PRB: Index Corruption Occurs in a Database That Is Upgraded from SQL Server 7.0 to SQL Server 2000"

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;298806

    Cheers,

    - Mark

    Edited by - mccork on 12/08/2003 03:39:54 AM


    Cheers,
    - Mark

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply