CHECKDB Error

  • I am getting CHECKDB error in one of my database. The DBCC results are as follows:

    Server: Msg 2511, Level 16, State 2, Line 1

    Table error: Object ID 1769773362, Index ID 0. Keys out of order on page (1:65957), slots 138 and 139.

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

    Table error: Object ID 1769773362, Index ID 0. Keys out of order on page (1:67929), slots 78 and 79.

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

    Table error: Object ID 1769773362, Index ID 0. Keys out of order on page (1:67929), slots 85 and 86.

    CHECKDB found 0 allocation errors and 3 consistency errors in table 'TableA' (object ID 1769773362).

    CHECKDB found 0 allocation errors and 3 consistency errors in database 'DbA'.

    If I were to execute DBCC DBREINDEX on the table or manually drop and re-create the indexes, it either shows no change or it moves the error to another table!(strange?)

    I have tried all three CHECKDB options (including with data loss), but it appears not to have done much good.

    I can select all the data without any issues, and running DBCC CHECKTABLE on the offending table does not result in any errors!

    We have requested the network guys to have a look at the hardware side of things, but any pointers at this stage would be greatly appreciated.

    NB: SQL Server 2000 Standard Edition with SP3 installed.

    Paul

  • There have been some bugs with column comparisons on 2000 that are fixed in SP4:

    http://support.microsoft.com/kb/822747

    http://support.microsoft.com/kb/929440

    Hopefully one of these is your issue - it doesn't sound like hardware is your problem.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Thanks Paul - I was hoping you might be available to help out.

    I have just moved to a new OPS team, still on 2000 SP3. Not sure why they have not apply SP4, but will find out and see if this can be fixed. Will get back once I have some news.

    Paul

  • As promised, here is the resolution for this problem:

    1. SP4 was installed on the offending server;

    2. DBCC CHECKDB and DBCC CHECKTABLE now showing the same error; that is, the message returned from the DBCC commands were now consistent;

    3. Offending table now identified;

    4. Set database to single-user mode;

    5. Executing DBCC CHECKTABLE with the various REPAIR options did not fix the problem;

    6. Drop all indexes and constraints on the table;

    7. Rerun DBCC CHECKTABLE with REPAIR FAST option - no error message 🙂

    8. Identify and remove offending duplicate records;

    9. Re-apply all indexes and constraints;

    10. rerun DBCC CHECKTABLE;

    11. Reset database to multi-user mode;

    HTH

    Paul

  • Thank you both for this thread. Had a customer database

    and they started getting this error a month ago. 8 million rows in the table and BCP and DTS failed due to the high number of errors.

    Ran through the steps and although we lost about 800K rows, it was old performance data that we, sad to say, could afford to lose.

    Database is clean again and will be moved to a new server this week.

Viewing 5 posts - 1 through 4 (of 4 total)

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