CHECKDB Error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » CHECKDB Error

CHECKDB Error

Rate Topic

Display Mode

Topic Options

Author

Message

Paul Mu

Posted Wednesday, January 30, 2008 9:50 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 9:42 AM
Points: 339, Visits: 386

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

Post #449520

Paul Randal

Posted Wednesday, January 30, 2008 6:44 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Post #449769

Paul Mu

Posted Thursday, January 31, 2008 3:17 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 9:42 AM
Points: 339, Visits: 386

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

Post #449845

Paul Mu

Posted Thursday, March 06, 2008 10:13 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 9:42 AM
Points: 339, Visits: 386

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 Smile

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

Post #465331

DMarvez

Posted Thursday, August 13, 2009 11:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 10:15 AM
Points: 40, Visits: 264

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.

Post #770351

« Prev Topic | Next Topic »

Permissions