{"id":916,"date":"2009-03-22T06:14:00","date_gmt":"2009-03-22T06:14:00","guid":{"rendered":"\/blogs\/paul\/post\/IAM-page-corruption-examples.aspx"},"modified":"2013-01-01T21:12:08","modified_gmt":"2013-01-02T05:12:08","slug":"iam-page-corruption-examples","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/iam-page-corruption-examples\/","title":{"rendered":"IAM page corruption examples"},"content":{"rendered":"

\nA thread cropped up on SQLServerCentral involving IAM chain corruption (see <\/font>Inside the Storage Engine: IAM pages, IAM chains, and allocation units<\/font><\/a> for details of IAM chains). The error from DBCC CHECKDB was: <\/font>\n<\/p>\n

\n

\n\tServer: Msg 2576, Level 16, State 1, Line 1
\n\tIAM 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. <\/font>\n\t<\/p>\n<\/blockquote>\n

\nand 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. <\/font>\n<\/p>\n

\nWe'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. <\/font>\n<\/p>\n

\n

\n\tCREATE DATABASE CorruptIAMEXample;
\n\tGO
\n\tUSE CorruptIAMExample;
\n\tGO <\/font>\n\t<\/p>\n

\n\tCREATE TABLE test (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
\n\tGO
\n\tCREATE CLUSTERED INDEX test_cl on test (c1);
\n\tGO <\/font>\n\t<\/p>\n

\n\tSET NOCOUNT ON;
\n\tGO
\n\tINSERT INTO test DEFAULT VALUES;
\n\tGO 1000<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

After corrupting the database, I can reproduce the error above by running DBCC CHECKDB on it:<\/font><\/font><\/font> <\/font><\/p>\n

\n

\n\tMsg 2576, Level 16, State 1, Line 1
\n\t<\/font>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. <\/font><\/font>\n\t<\/p>\n<\/blockquote>\n

<\/font><\/p>\n

\nThe error is saying that the first IAM page in the IAM chain (page 1:153) does not have a reference from metadata. The sysallocunits<\/font> 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<\/font> catalog view, or you can see this blog post  – <\/font>Inside The Storage Engine: sp_AllocationMetadata – putting undocumented system catalog views to work<\/font><\/a>. I corrupted the sysallocunits<\/font> 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. <\/font>\n<\/p>\n

\nNow 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<\/font>: <\/font>\n<\/p>\n

<\/p>\n

\n

\n\tMsg 2575, Level 16, State 1, Line 1
\n\t<\/font>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.
\n\t<\/font>Msg 7965, Level 16, State 2, Line 1
\n\t<\/font>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).
\n\t<\/font>Msg 8928, Level 16, State 6, Line 1
\n\t<\/font><\/font>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.<\/font> <\/font><\/font>\n\t<\/p>\n<\/blockquote>\n

<\/font><\/p>\n

\nand a whole bunch of <\/font>\n<\/p>\n

<\/font><\/p>\n

\n

\n\tMsg 8905, Level 16, State 1, Line 1
\n\t<\/font>Extent (1:216) in database ID 21 is marked allocated in the GAM, but no SGAM or IAM has allocated it.<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

<\/font><\/p>\n

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

\nI've created a zipped backup of the SQL 2005 database in each case: <\/font>\n<\/p>\n