6

Running DBCC CHECKDB (dbname) WITH NO_INFOMSGS, ALL_ERRORMSGS results in this error:

Table error: Multiple IAM pages for object ID 813245952, index ID 1, partition ID 72057594073972736, alloc unit ID 72057594083213312 (type In-row data) contain allocations for the same interval. IAM pages (1:4611577) and (1:2040821). CHECKDB found 1 allocation errors and 0 consistency errors in table 'tablename' (object ID 813245952). CHECKDB found 1 allocation errors and 0 consistency errors in database 'dbname'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbname).

Has anyone experienced this issue before and have a solution for repair?

Note:- I have not run the statement with repair_allow_data_loss yet.

10
  • Do you have good backup to restore ?
    – Kin Shah
    May 12, 2015 at 20:12
  • We have backups we can restore May 12, 2015 at 20:17
  • Dbcc tells you that data loss is your only option. Best is to restore the db from backup. This is commonly caused by hardware failure. Can you check windows event log for any trails? Refer : technet.microsoft.com/en-us/library/aa275771(v=sql.80).aspx
    – Kin Shah
    May 12, 2015 at 20:31
  • Can you restore without data loss? Do that. If not you can salvage data from the database. I wonder how this particular corruption happened? Reads like a duplicate IAM page?! Did you rebuild the log or do some other "risky" procedure?
    – usr
    May 12, 2015 at 20:52
  • Is index ID 1 a clustered index or a heap? If not you can drop it and scan the database again. But restore the database on the side and check that its error free as well
    – Spörri
    May 12, 2015 at 22:42

1 Answer 1

0

Several years after op. We ran into this error on SQL Server 2014. Rebuilding the clustered Index on the table fixed the error

ALTER INDEX pk_table ON dbo.table REBUILD WITH (ONLINE = OFF)

after that DBCC CHECKDB (MyDatabase) ran with no errors. 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.