IAM page corruption examples

A thread cropped up on SQLServerCentral involving IAM chain corruption (see Inside the Storage Engine: IAM pages, IAM chains, and allocation units for details of IAM chains). The error from DBCC CHECKDB was:

Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) object ID 229575856 index ID 4 but was not detected in the scan.

and there was some discussion of what the error meant, and why the initial page ID in the error of (0:0) means something special. There was a further question of how the errors would differ if the IAM page header was partially zero'd out by an I/O subsystem error.

We're on-stage here at SQL Connections doing a pre-con and I'm not on until this afternoon so I can bang out a quick blog post! I'm going to create a small database and show the difference between the two cases. The error above was from a SQL 2000 database, but the behavior is the same on SQL 2005 and 2008. Here's the script to create the database.

CREATE DATABASE CorruptIAMEXample;
GO
USE CorruptIAMExample;
GO

CREATE TABLE test (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
GO
CREATE CLUSTERED INDEX test_cl on test (c1);
GO

SET NOCOUNT ON;
GO
INSERT INTO test DEFAULT VALUES;
GO 1000

After corrupting the database, I can reproduce the error above by running DBCC CHECKDB on it:

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:153) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594042384384 (type Unknown), but it was not detected in the scan.

The error is saying that the first IAM page in the IAM chain (page 1:153) does not have a reference from metadata. The sysallocunits system table contains a link to the first IAM page, the root page, and the first page. You can see these by querying the sys.system_internals_allocation_units catalog view, or you can see this blog post  – Inside The Storage Engine: sp_AllocationMetadata – putting undocumented system catalog views to work. I corrupted the sysallocunits table so that the link to the first IAM page of the test table was removed. The clue is the first page ID in the error – if it's a (0:0), that's the missing metadata case. For SQL 2000, this can happen if someone manually updates the sysindexes table.

Now what about the other case, where the IAM page itself has a corrupt header? I recreated the database again and corrupted the header of the first IAM page of the test table. Here's the output from DBCC CHECKDB:

Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:153) is pointed to by the next pointer of IAM page (0:0) in object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594042384384 (type In-row data), but it was not detected in the scan.
Msg 7965, Level 16, State 2, Line 1
Table error: Could not check object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594042384384 (type In-row data) due to invalid allocation (IAM) page(s).
Msg 8928, Level 16, State 6, Line 1
Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:153) could not be processed. See other errors for details.

and a whole bunch of

Msg 8905, Level 16, State 1, Line 1
Extent (1:216) in database ID 21 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

errors. This is because the IAM page no longer looks like an IAM page and so DBCC CHECKDB can't process it as such. If I zero out the IAM page completely, DBCC CHECKDB returns basically the same errors as above. 

I've created a zipped backup of the SQL 2005 database in each case:

You'll need to do a RESTORE FILELISTONLY and then maybe move the files when you restore. Have fun!

17 thoughts on “IAM page corruption examples

  1. Hi Paul,
    Can you give a hint (no solution, just hints) on how to solve the first problem in example 1?

    Best regards and thanks
    Søren Agerbo

  2. Hi Paul, me again :D

    the last week I ask you, my DB is being recovered and wait until it finished.
    for now my DB is turning back to EMERGENCY mode.
    It has changed after restart a whole windows server.

    I am trying to check consistency of my DB using DBCC CHECKDB, then i have get a new error message.
    An error is same like your post.

    “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:505354) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594043498496 (type Unknown), but it was not detected in the scan.”

    Could you please to tell me how to resolve this? and turning back my DB online without missing rows of table?

    Many thanks.

    1. Can I create a new database, and put all of data from DB emergency mode into a new databases using “SELECT INTO” clause? then drop an old DB.

      It’s okay to do this?

      1. Yes, it’s not quite that simple, but basically yes. Note that the data you recover will be transactionally inconsistent. I’d only do this if there’s no way to recover using a backup.

  3. Hi paul ,
    I have the same error

    The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:1289162) in object ID 950768189, index ID 4, partition ID 72057618507497472, alloc unit ID 72057618483052544 (type In-row data), but it was not detected in the scan.

    I have run the sys allocation view and

    What I think has happened is the xx table was dropped and recreated. This means that the object ID for this table has changed and the corresponding internal table was dropped when the xx was recreated. Therefore there are now four IAM pages pointing to an object (the internal table) that no longer exists.

    what do you mean by repair plsss???
    can i have a solution pls

    1. No – it’s more likely that the metadata became corrupt somehow. If REPAIR_ALLOW_DATA_LOSS didn’t fix it, you’ll need to restore from your backups or export to a new database.

      1. is there any other option apart from restore please?
        i can not find the index either can only find records in , sys.partition and allocation .
        The table doesnt exist physically

  4. You could manually edit the system tables to remove the entries, but then your database becomes unsupported. Or you could export to a new database.

    Once you’ve decided which way to go, make sure to fix up your backup strategy so you don’t have to rely on repair next time a corruption occurs.

    1. Thank you very much for the reply Paul

      I have data rows in partition and alloc unit DMV’s , how can i manually edit or delete these rows , as the internal table doesnt exist any more and only the rows for the index exists

      1. You’ll need to read my blog post on manually editing system tables, and extrapolate that to the sys.sysrowsets and sys.sysallocunits tables. Again, that will make your database unsupported by Microsoft. I would advise exporting to a new database to fix this.

  5. Hi Paul,

    I have just downloaded the CorruptIAMExample1.zip & restored the database on my laptop. I am making use of SQL Server 2012.

    And before performing repair_rebuild I could clearly see 1000 records being present inside the database. And I have performed the below operations post which the data loss happened clearly.

    alter database corruptiamexample set single_user
    begin tran
    dbcc checkdb(‘corruptiamexample’,’repair_rebuild’)
    use corruptiamexample
    select * from test
    rollback

    My question is as per you repair_rebuid will not cause any data loss but how come data loss has happened in the above scenario???can you please correct me if am wrong?

    Thanks in Advance.

  6. Hi Paul,When i check checkdb in database then no any issue but when taken backup (in storage ) and restored (in other server) then after checkdb give below error
    Page (1:10) in database ID 6 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags ‘MIXED_EXT ALLOCATED 0_PCT_FULL’.

    But restored in other server then ok , no any issue .
    if other DB restored then no any error .
    Issue in drive or backup ?
    How to solve this issue.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.