Index Allocation Map Error

Question

i have a 1.6TB database which got corrupted due to some LUN issue. i fixed most of the corruption through DBCC (storage also checked out) but i still getting 1 allocation issue regardless how many times i run DBCC with allow data loss/repair/rebuild. DBCC reports that it fixed the allocation but if i run checkdb again it reports back 1 allocation error. at this point we do not have a 'clean' backup to restore from and i'm running out of ideas. any help is much appreciated. do i need reboot after each repair because the only thing i didnt do was reboot the machine since its production and i rebuilt indexes on each table that didnt help either

Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (13:1546477) is pointed to by the next pointer of IAM page (0:0) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057621802647552 (type Unknown), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 0 consistency errors in database 'mydb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (mydb).

Answer 1

You should restore the database from a clean backup. You may have probably damaged the database beyond hope of recovery with running REPAIR_ALLOW_DATA_LOSS. Keep in mind that what this option does when it can piece things together is to throw pages away, so you may lose very important data.

If you run things like REPAIR_ALLOW_DATA_LOSS, you should to do this on a copy of the database, preferably on a test server.

In this case, I guess the damage is not repairable. Or the new damages are uncovered as you move along. Rebooting the machine is not going to change anything. Or, well, unless the issue is not with the bits on disk itself, but a driver that is returning incorrect results. But in that case, DBCC typically produces wilder errors. (I had a client where DBCC crashed directly with an evil error message. Turned out that in the end that it was the RAID that was corrupt, and the database was OK, but the RAID sent SQL Server in the completely wrong direction when reading the data file.)

To repeat: restore from a clean backup.

Answer 2

DBCC cannot fix everything wrong with the database. You need to restore from a known good backup.

Answer 3

If you don't have a good backup, I am sorry, but you are in a very bad situation.

It is possible that the database can be repaired. There are a couple of tools out there that claim to do both this and that, but I have never used them, and I would not have much faith of anything that is point and click for a situation like this. Each corruption is unique.

It may also be possible to repair the database by looking at pages and repairing the database by writing directly to the pages (without SQL Server). This requires very good knowledge of how SQL Server stores the data, and it is very time-consuming. There might be people who can offer to help you with this, but you can count on that they have very stiff rates. And in the end, it may prove to not be possible, or what you get back is only half of it.

Is it possible to access the database at all? In that case, the best may be to extract scripts, create a new database, and try to extract as much data as you can. If you find that there is important data you can get at, it may be possible to get these with a limited amount of bit-fiddling. But unless you were smart enough to take a backup before you run REPAIR_ALLOW_DATA_LOSS, you may already have thrown that data out the window.