?
Solved

SQL DBCC CHECKDB finds errors that it cannot fix

Posted on 2009-12-23
6
Medium Priority
?
1,749 Views
Last Modified: 2012-05-08
Hello,

I am getting errors that are not able to be cleaned out with a DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS.  I am running the following statement:

DBCC CHECKDB ('dbname', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS,NO_INFOMSGS
GO

And I am getting the output below.  I get the errors again when I run it a second time - so it's not cleaning them up.  I get the same errors off of my backups, so it must be an issue that's been around for awhile.

Repair: IAM chain for object ID 1268915592, index ID 1, partition ID 72057597531455488, alloc unit ID 72057597595287552 (type In-row data), has been truncated before page (1:612342) and will be rebuilt.
Repair: IAM chain for object ID 1268915592, index ID 2, partition ID 72057597531521024, alloc unit ID 72057597595353088 (type In-row data), has been truncated before page (1:544268) and will be rebuilt.
Repair: IAM chain for object ID 1268915592, index ID 3, partition ID 72057597531586560, alloc unit ID 72057597595418624 (type In-row data), has been truncated before page (1:678756) and will be rebuilt.
Repair: IAM chain for object ID 1268915592, index ID 4, partition ID 72057597531652096, alloc unit ID 72057597595484160 (type In-row data), has been truncated before page (1:688003) and will be rebuilt.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:612342) in object ID 1268915592, index ID 1, partition ID 72057597531455488, alloc unit ID 72057597595287552 (type In-row data), but it was not detected in the scan.
        The error has been repaired.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:544268) in object ID 1268915592, index ID 2, partition ID 72057597531521024, alloc unit ID 72057597595353088 (type In-row data), but it was not detected in the scan.
        The error has been repaired.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:678756) in object ID 1268915592, index ID 3, partition ID 72057597531586560, alloc unit ID 72057597595418624 (type In-row data), but it was not detected in the scan.
        The error has been repaired.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:688003) in object ID 1268915592, index ID 4, partition ID 72057597531652096, alloc unit ID 72057597595484160 (type In-row data), but it was not detected in the scan.
        The error has been repaired.
CHECKDB found 4 allocation errors and 0 consistency errors in table '(Object ID 1268915592)' (object ID 1268915592).
CHECKDB fixed 4 allocation errors and 0 consistency errors in table '(Object ID 1268915592)' (object ID 1268915592).
CHECKDB found 4 allocation errors and 0 consistency errors in database 'clarkitbiz'.
CHECKDB fixed 4 allocation errors and 0 consistency errors in database 'clarkitbiz'.

Any ideas?  Thank you!
0
Comment
Question by:clarkincit
  • 3
  • 2
6 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26116833
Seems like an error with some of your Index data..
Run this

USE ur_db_name
GO
Exec sp_MSForEachtable 'ALTER INDEX ALL ON ? REBUILD;'
GO

and then try using your earlier DBCC script

DBCC CHECKDB ('ur_db_name', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS,NO_INFOMSGS
GO

Now it should probably fix it.

And hope you are running DBCC CHECKDB in single user mode right..
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 26123413
Ummmm... If you are already running (as the only way to fix) :

DBCC CHECKDB ('dbname', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS,NO_INFOMSGS
GO

Now you can check what table it is :

select object_name(1268915592,db_id('clarkitbiz'))  

It does look like it is fixing without losing any data, so not exactly sure what the problem is - it should be a one time "fix".

So, best to maybe drop and then manually rebuild indexes on that table (after the fix), and to take a full backup.
0
 

Author Comment

by:clarkincit
ID: 26147656
Thank you for your responses.  

When I try to find the object associated with that id, there is no object found.  What do I do then?

When I try to rebuild all of the indexes, I get the following error:

ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Is there an easy way to find what the offending indexes are?  

Thank you,
Christine
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 26153311
I really should apologise, the error message normally shows the table name followed by the object_ID.

In your case it is not showing a table name but instead shows '(Object ID 1268915592)'  as if it were the table name.

That is not good.  It means that it is reporting an error for a database object that doesnt exist and most likely means PFS pages corruption.

How big is your database ? Are you actually missing any tables ? Is this a 2008 database ?

The "yucky" fix is to script out your tables (and procedures, etc etc etc etc) and build a new database. If it is 2008 then you should be able to use the export wizard, but this kind of fix can get intense - not overly difficult as such - just tedious and have to be very accurate with rebuilding indexes, referential integrity, procedurures, scripts etc...



0
 

Author Comment

by:clarkincit
ID: 26185587
Wow - that is "yuck"!  The database is pretty large.  I am not aware of any missing tables and yes, it is a 2008 database.

I will have to plan this out.  Thanks for the feedback.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26186404
Hi, yeah, not so nice, but at least SQL2008 has a pretty good export wizard.

Do you have complex foreign keys and constraints in there ? If not it does become a lot easier...

Could even create another database on the same server and load that directly.

I guess the one saving grace is that you say it appears to have been there for some time, so it doesn't appear to be affecting "live", gives you a chance to trial a few recoveries...

Have been trolling through Microsoft as well, and think I saw you there :)

If you find another "fix", could you please let us know, it would be most appreciated.

Cheers,
Mark Wills

0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question