June 21, 2011 at 1:58 pm
Hello,
I have some allocation errors and am unsure what to do. Unfortunately restoring a backup isn’t an option since this error happens on all restores of previous backups. We weren’t having any trouble that made me run this, as far as I knew everything was working fine.
I notice the errors mention index IDs related to the same object ID. Is there any way to determine which table that refers to and delete/recreate the indexes? Gail’s article doesn’t cover this error (http://www.sqlservercentral.com/articles/Corruption/65804/), but I found a post where Paul Randal suggests some advice for a similar issue – but that’s when the table in question is known (http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/10949/Database-Consistency-Error). Anyone have any advice? Thanks.
SQL 2008
dbcc checkdb (MYDB) WITH NO_INFOMSGS, ALL_ERRORMSGS
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:612342) in object ID 1268915592, index ID 1, partition ID 72057597531455488, alloc unit ID 72057597595287552 (type In-row data), but it was not detected in the scan.
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:544268) in object ID 1268915592, index ID 2, partition ID 72057597531521024, alloc unit ID 72057597595353088 (type In-row data), but it was not detected in the scan.
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:678756) in object ID 1268915592, index ID 3, partition ID 72057597531586560, alloc unit ID 72057597595418624 (type In-row data), but it was not detected in the scan.
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:688003) in object ID 1268915592, index ID 4, partition ID 72057597531652096, alloc unit ID 72057597595484160 (type In-row data), but it was not detected in the scan.
CHECKDB found 4 allocation errors and 0 consistency errors in table '(Object ID 1268915592)' (object ID 1268915592).
CHECKDB found 4 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).
June 21, 2011 at 2:28 pm
Not sure about rebuilding, in your case it's the clustered index affected (the actual data in the table). The case in the ref was a nonclustered index (redundant copy of the data)
If you have a copy of the DB on a test server, you can try dropping all NC indexes, then dropping the cluster, then recreating them all, but I suspect something in there will fail.
Do you have a clean backup?
Edit: I'm not sure about this one at all, have asked for a second opinion.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2011 at 2:45 pm
As for the table, that's easy (or should be). Take the objectID (1268915592) and query sys.tables. Otherwise what you could do (which should have the same result) is take the Partition ID (partition ID 72057597531455488) and query sys.partitions join to sys.tables and get the table name from that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2011 at 2:49 pm
p.s. Is this a SQL 2000 upgrade? If so is it a recent upgrade?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2011 at 2:49 pm
Thanks for the replies! I'll look into these options and report back in the morning.
June 21, 2011 at 2:52 pm
GilaMonster (6/21/2011)
p.s. Is this a SQL 2000 upgrade? If so is it a recent upgrade?
No, this was an upgrade from 2005 a few years back.
June 22, 2011 at 5:52 am
Ok, I checked with an expert on this.
If this is the only error, then checkDB with repair_allow_data_loss should fix this with no data loss. Take a backup first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2011 at 1:13 pm
Thanks for your help Gail, sorry for the delay in response! I was finally able to get a copy of the database and a machine to test it on today.
Unfortunately, I haven't had much luck. I've ran the Repair command 3 times and although it originally states that the error has been repaired, the same errors are shown. When I query sys.tables on the object_id shown, nothing gets returned. Does that mean the table has been deleted? When I query sys.partitions, I get the same object_id that is mentioned in the original error.
Any other advice?
dbcc checkdb (6, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS
Repair: IAM chain for object ID 1268915592, index ID 1, partition ID 72057597531455488, alloc unit ID 72057597595287552 (type In-row data), has been truncated before page (1:612342) and will be rebuilt.
Repair: IAM chain for object ID 1268915592, index ID 2, partition ID 72057597531521024, alloc unit ID 72057597595353088 (type In-row data), has been truncated before page (1:544268) and will be rebuilt.
Repair: IAM chain for object ID 1268915592, index ID 3, partition ID 72057597531586560, alloc unit ID 72057597595418624 (type In-row data), has been truncated before page (1:678756) and will be rebuilt.
Repair: IAM chain for object ID 1268915592, index ID 4, partition ID 72057597531652096, alloc unit ID 72057597595484160 (type In-row data), has been truncated before page (1:688003) and will be rebuilt.
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:612342) in object ID 1268915592, index ID 1, partition ID 72057597531455488, alloc unit ID 72057597595287552 (type In-row data), but it was not detected in the scan.
The error has been repaired.
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:544268) in object ID 1268915592, index ID 2, partition ID 72057597531521024, alloc unit ID 72057597595353088 (type In-row data), but it was not detected in the scan.
The error has been repaired.
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:678756) in object ID 1268915592, index ID 3, partition ID 72057597531586560, alloc unit ID 72057597595418624 (type In-row data), but it was not detected in the scan.
The error has been repaired.
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:688003) in object ID 1268915592, index ID 4, partition ID 72057597531652096, alloc unit ID 72057597595484160 (type In-row data), but it was not detected in the scan.
The error has been repaired.
CHECKDB found 4 allocation errors and 0 consistency errors in table '(Object ID 1268915592)' (object ID 1268915592).
CHECKDB fixed 4 allocation errors and 0 consistency errors in table '(Object ID 1268915592)' (object ID 1268915592).
CHECKDB found 4 allocation errors and 0 consistency errors in database 'MyDB'.
CHECKDB fixed 4 allocation errors and 0 consistency errors in database 'MyDB'.
June 27, 2011 at 2:45 pm
Well i did say 'if that's the only error' and from the repair output it's clear that was not the only error.
If repair allow data loss won't repair, then the error is not repairable. Looks like something has damaged the system tables somehow.
If you have no clean backup, script, export, recreate what you can.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2011 at 7:08 am
Oh gotcha, I thought you meant the only type of error, not how many of them there are. Thanks anyway.
Any thoughts on what a worst case scenario (or any scenario really) would be if this didn't get corrected. Judging by the face it's in the development DB too, it's been this way for at least a year and hasn't given us any problems that I'm aware of. I'm assuming it should be corrected, but I'm sure management is going to want to know the benefits of exporting everything to a new DB.
June 28, 2011 at 1:49 pm
I did mean the only type of error, but there are two types of allocation errors in your checkDB output.
If you don't fix it, queries that hit the corruption will fail and be disconnected. The DB could come up suspect after a restart, nasty things like that.
If it's a dev database, drop it and replace with a cut-down, edited copy of production.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2011 at 8:00 am
Okay, thanks for your help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply