CHECKDB Error

Question

Post reply

CHECKDB Error

Paul Mu

SSCertifiable

Points: 5547
More actions
January 30, 2008 at 9:50 am

#182380

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

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

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

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

Paul Mu SSCertifiable Points: 5547 More actions · Answer 2

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

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

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

DMarvez Ten Centuries Points: 1241 More actions · Answer 4

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.