CHECKDB ERROR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

CHECKDB ERROR

Author

Topic

DBA007
Posting Yak Master

145 Posts

Posted - 2010-01-15 : 15:03:47

I have a MPlan it was failing when i run the DBCC CHECKDB(dbname)with NO_INFOMSGS,ALL_ERRORMSGS,i got the error as below any solution?
Msg 8928, Level 16, State 1, Line 1
Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 57266813337600 (type In-row data): Page (1:175852) could not be processed. See other errors for details.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 57266813337600 (type In-row data). Page (1:175853) is missing a reference from previous page (1:175852). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 338741790048256 (type In-row data). Page (1:175852) was not seen in the scan although its parent (1:175747) and previous (1:175851) refer to it. Check any previous errors.
Msg 8944, Level 16, State 24, Line 1
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 338741790048256 (type In-row data), page (1:175852), row 40. Test (ColumnOffsets + (int)sizeof (UINT16) <= (nextRec - pRec)) failed. Values are 174 and 172.
Msg 8944, Level 16, State 24, Line 1
Table error: Object ID 873822225, index ID 1, partition ID 338741790048256, alloc unit ID 338741790048256 (type In-row data), page (1:175852), row 40. Test (ColumnOffsets + (int)sizeof (UINT16) <= (nextRec - pRec)) failed. Values are 174 and 172.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'NOC' (object ID 873822225).
CHECKDB found 0 allocation errors and 5 consistency errors in database 'Med'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Med).

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-16 : 03:29:56

Is that the full output to CheckDB?

Get your last full backup out (assuming that it isn't also corrupt). Take a tail log backup of the database, then restore the full backup, all your log backups and end with the tail log backup that you just took. That should get the DB clean and with no data loss.

Also, do some root cause analysis as to what may have caused this. Look for any IO or disk-related errors in the Windows event logs.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 04:41:54

I would suggesting doing a restore (in the way Gail describes) to a fresh, temporary database, and DBCC CHECKDB that before doing it for real on the live database.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-16 : 05:15:19

Unfortunately, it seems (based on the answers given over at SSC) that the OP has no backup. http://www.sqlservercentral.com/Forums/Topic848526-146-1.aspx

So looks like another case where data is lost because no one gave any thought to the importance of having backups....

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 05:39:01

Oh dear.

I've been able to recover databases in that state by exporting the data from tables one-by-one.

Sometimes I get to a point where there is damage and I can export using

SELECT * FROM MyTable WHERE PKey < @Damage

plus

SELECT * FROM MyTable WHERE PKey >= @AfterDamage

and sometimes that works if I first get a list of PKs from a secondary index (using a query that is covered by the index) and use that as a basis to export data in parts / sections.

But its all-bets-off at that point, and my top-$ fee because I have no sympathy, and no charity, in those circumstances - as distinct from good backups but some addition catastrophe that rendered them unusable.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-16 : 07:30:46

Don't think the export/import is necessary here. It's a simple corruption and CheckDB when run with the repair_allow_data_loss will be able to fix this, albeit with some data loss. Specifically one page of rows from the NOC table.

There is no way this is going to be fixed without data loss, the clustered index is damaged and there's no backup.
Like Kristen, I have little sympathy for the 'corrupt database, no backup' scenario. If the DB was important, there should be backups.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources