DBCC Checkdb consistency errors

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » DBCC Checkdb consistency errors

DBCC Checkdb consistency errors

Rate Topic

Display Mode

Topic Options

Author

Message

Yulia Fuller-382525

Posted Saturday, April 26, 2008 6:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 8:29 AM
Points: 2, Visits: 115

Hello All,

After restoring the SQL Server 2000 user database backup into one of the SQL Server 2000 instances I get database consistency errors. I restored this database a couple of times and every time I get the errors; however they are not consistent - different types of errors, different tables and indexes get affected. The first time this happened I rebuilt the indexes and it resolved the errors. This time rebuilding the indexes didn't help. I tried all dbcc checkdb options and finally used the last one -repair_allow_data_loss.

I restored the same database backup into a different server and ran dbcc checkdb over it - no errors occurred. Something causes corruption in the database on one particular server.

Here are the errors:

DBCC results for 'tTREE_COMPONENTS'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1467308437, index ID 0: Page (1:251481) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.

DBCC results for 'tACCUMULATORS'.
There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'tACCUMULATORS' (object ID 1467308437).

DBCC results for 'WATSTARTDATETP'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1905493917, index ID 4: Page (3:224866) could not be processed. See other errors for details.

Server: Msg 8941, Level 16, State 1, Line 1
Table error: Object ID 1905493917, index ID 4, page (3:224866). Test (sorted [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.

Server: Msg 8942, Level 16, State 1, Line 1
Table error: Object ID 1905493917, index ID 4, page (3:224866). Test (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the prior row.
There are 4 rows in 1 pages for object 'WATSTARTDATETP'.

DBCC results for 'ACCRUALTRAN'.
There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'ACCRUALTRAN' (object ID 1905493917).

How would you determine the cause of the database corruption in SQL Server 2000 environment?

If you could please take a look at the errors and give me your thoughts it would be greatly appreciated.

Thank you in advance,

Yulia

Post #491094

Paul Randal

Posted Sunday, April 27, 2008 6:02 PM

SSCommitted

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

Sounds like the hardware on the first server is dodgy in some way - if repeated restores on that server result in corruptions but restores on other servers don't, then something in the IO subsystem on that server is broken. I would run SQLIOSim on the server to see if it can flush out any problems.

Thanks

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 #491162

Yulia Fuller-382525

Posted Monday, April 28, 2008 1:58 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 8:29 AM
Points: 2, Visits: 115

Paul,

Thank you so much for your advice! I asked our server administrators to run Dell diagnostics but in the meantime I will run SQLIOSim independently.

I know that you rewrote dbcc checkdb and you are the guru in the database repair. I ran the consistency checker several times over this “problem” SQL Server database and I was surprised by the results. DBCC checkdb with the repair_allow_data_loss option repaired 9 errors; however when I ran dbcc checkdb (dbname) 18 minutes later it detects 714 errors. Does it make sense for you?

2008-04-25 10:51:08.27 spid53 DBCC CHECKDB (tkcsdb) executed by yfuller found 5 errors and repaired 0 errors.

2008-04-25 11:33:27.64 spid54 DBCC CHECKDB (tkcsdb, repair_fast) executed by yfuller found 9 errors and repaired 0 errors.

2008-04-25 12:02:42.90 spid54 DBCC CHECKDB (tkcsdb, repair_allow_data_loss) executed by yfuller found 9 errors and repaired 9 errors.

2008-04-25 12:20:25.34 spid54 DBCC CHECKDB (tkcsdb) executed by yfuller found 714 errors and repaired 0 errors.

2008-04-25 12:55:28.81 spid54 DBCC CHECKDB (tkcsdb, repair_fast) executed by yfuller found 714 errors and repaired 714 errors.

2008-04-25 13:07:32.21 spid54 DBCC CHECKDB (tkcsdb) executed by yfuller found 0 errors and repaired 0 errors.

Sincerely,

Yulia

Post #491703

Steve Jones - SSC Editor

Posted Monday, April 28, 2008 2:13 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

Repair is a bad idea. As Paul gave a talk last year at TechEd and walked through what happens. Basically they have no idea how to fix things and they start making guesses. He didn't recommend the allow_data_loss.

You''d be better off calling PSS and having them help you get the data out and rebuild the table.

At this point, I'd run some rowcounts and try to determine if you've lost data and perhaps just call PSS. If you have Technet or MSDN, you have some free incidents. If not, buy TechNet ($349) and you get two incidents (normally $249)

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #491711

Paul Randal

Posted Monday, April 28, 2008 3:32 PM

SSCommitted

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

Well, Steve - it's not that bad. We know how to fix all errors that are detected - there's no guessing involved in repair. What you're thinking of is the online checking algorithm in SS2000 that in a couple of pathalogical cases had to guess as to some log record meanings - *never* in repair.

Yes, the behavior you're seeing is consistent with my experience sometimes. A corruption may be 'hiding' other corruptions by preventing deeper checking algorithms from running - once the repair is fixed, as subsequent run of CHECKDB may find more errors bevcause other algorithms are now able to run.

I'd never run repairs without restoring backups first - you will have lost data from your database by doing so (REPAIR_ALLOW_DATA_LOSS is very aptly named).

Thanks

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 #491755

Yulia Fuller

Posted Monday, April 28, 2008 6:12 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2008 7:21 PM
Points: 1, Visits: 10

Paul and Steve,

First of all, thank you so much for your professional advice! I really appreciate it!

I just want to bring to your attention that I wouldn't do anything like that in the production environment. I was testing the backup-restore for one of our prod databases on the test servers. The restores on one particular test server resulted in corruption but the restores on other servers didn't - I was testing the same database backup file.

I kept restoring the backup to this "problematic" test server and I ran different dbcc checkdb options over it and documented the errors.

Per Paul's advice, I am currently running the SQLIOSim on the server with the database corruption.

Thank you,
Sincerely,

Yulia

Post #491788

« Prev Topic | Next Topic »

Permissions