Receiving error when running DBCC CheckDB - multiple IAM pages

Question

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.

• Kin Shah: 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

Answer 1

We ran into this error on SQL Server 2014, and resolved it by rebuilding the clustered index on the table.

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

Afterwards DBCC CHECKDB (MyDatabase) ran with no errors.