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;
CREATE TABLE test (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
CREATE CLUSTERED INDEX test_cl on test (c1);
SET NOCOUNT ON;
INSERT INTO test DEFAULT VALUES;
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:
Corrupt metadata: CorruptIAMExample1.zip (185.51 kb)
Corrupt IAM page header: CorruptIAMExample2.zip (181.48 kb)
Zero'd IAM page: CorruptIAMExample3.zip (168.68 kb)
You'll need to do a RESTORE FILELISTONLY and then maybe move the files when you restore. Have fun!