dbcc question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

dbcc question

Author

Topic

helpme
Posting Yak Master

141 Posts

Posted - 2010-05-20 : 10:28:18

I have run DBCC CHECKDB ('abc') WITH NO_INFOMSGS, ALL_ERRORMSGS on a database and get the following messages:

------------------------------------------------------------

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:6823) in object ID 429244584, index ID 1, partition ID 72057594047168512, alloc unit ID 72057594053656576 (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:6821) in object ID 429244584, index ID 2, partition ID 72057594047234048, alloc unit ID 72057594053722112 (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:6825) in object ID 429244584, index ID 3, partition ID 72057594047299584, alloc unit ID 72057594053787648 (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:6827) in object ID 429244584, index ID 4, partition ID 72057594047365120, alloc unit ID 72057594053853184 (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 429244584)' (object ID 429244584).

CHECKDB found 4 allocation errors and 0 consistency errors in database 'abc'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (abc).

--------------------

after this, i run DBCC CHECKDB (abc, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS and get the following, which appear to indicate that it has repaired the errors

--------------------------------

Repair: IAM chain for object ID 429244584, index ID 1, partition ID 72057594047168512, alloc unit ID 72057594053656576 (type In-row data), has been truncated before page (1:6823) and will be rebuilt.
Repair: IAM chain for object ID 429244584, index ID 2, partition ID 72057594047234048, alloc unit ID 72057594053722112 (type In-row data), has been truncated before page (1:6821) and will be rebuilt.
Repair: IAM chain for object ID 429244584, index ID 3, partition ID 72057594047299584, alloc unit ID 72057594053787648 (type In-row data), has been truncated before page (1:6825) and will be rebuilt.
Repair: IAM chain for object ID 429244584, index ID 4, partition ID 72057594047365120, alloc unit ID 72057594053853184 (type In-row data), has been truncated before page (1:6827) 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:6823) in object ID 429244584, index ID 1, partition ID 72057594047168512, alloc unit ID 72057594053656576 (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:6821) in object ID 429244584, index ID 2, partition ID 72057594047234048, alloc unit ID 72057594053722112 (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:6825) in object ID 429244584, index ID 3, partition ID 72057594047299584, alloc unit ID 72057594053787648 (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:6827) in object ID 429244584, index ID 4, partition ID 72057594047365120, alloc unit ID 72057594053853184 (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 429244584)' (object ID 429244584).
CHECKDB fixed 4 allocation errors and 0 consistency errors in table '(Object ID 429244584)' (object ID 429244584).
CHECKDB found 4 allocation errors and 0 consistency errors in database 'abc'.
CHECKDB fixed 4 allocation errors and 0 consistency errors in database 'abc'.

----------------------------------------------

however, if i run DBCC CHECKDB ('abc') WITH NO_INFOMSGS, ALL_ERRORMSGS again, i appear to get the same errors:

----------------------------------------------

Msg 2576, Level 16, State 1, Line 2
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:6823) in object ID 429244584, index ID 1, partition ID 72057594047168512, alloc unit ID 72057594053656576 (type In-row data), but it was not detected in the scan.
Msg 2576, Level 16, State 1, Line 2
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:6821) in object ID 429244584, index ID 2, partition ID 72057594047234048, alloc unit ID 72057594053722112 (type In-row data), but it was not detected in the scan.
Msg 2576, Level 16, State 1, Line 2
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:6825) in object ID 429244584, index ID 3, partition ID 72057594047299584, alloc unit ID 72057594053787648 (type In-row data), but it was not detected in the scan.
Msg 2576, Level 16, State 1, Line 2
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:6827) in object ID 429244584, index ID 4, partition ID 72057594047365120, alloc unit ID 72057594053853184 (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 429244584)' (object ID 429244584).
CHECKDB found 4 allocation errors and 0 consistency errors in database 'abc'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (abc).

---------------------------------------------------

it appears that nothing is really being repaired. Am i out of luck here? This database was moved into my realm yesterday, so I'm not sure how long this error has existed.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2010-05-20 : 13:04:15

The 2576 errors indicate that there's no entry in metadata for this table and its 3 indexes (I made (0:0) a special case that really means 'metadata').

Was this an upgraded database from 2000 where someone had tried to delete this table by manually hacking the system tables? Or has someone manually changed the system tables in 2005+?

Repair should fix this - let me do some digging.

Do you have backups?

Paul S. Randal,
CEO, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet and SQL Server Magazines
Author of SQL 2005 DBCC CHECKDB/repair code

helpme
Posting Yak Master

141 Posts

Posted - 2010-05-20 : 14:06:15

I'm not sure if we have a good backup or not (I'll have to check with someone else on that, it just became my responsibility yesterday). Same with the 'manual hacking' on the system tables, I'll see if I can find out anything.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2010-05-20 : 14:12:17

Hmm - seems like this may be a case where repair can't tell what to do. It's going to rebuild the IAM chain, but it won't hook it into metadata if the metadata isn't complaining. You may be out of luck here.

Can you try DBCC CHECKTABLE (7) in the database? That runs consistency checks on the hidden system table sysallocunits, where the heads of the IAM chains are rooted.

Thanks

Paul S. Randal,
CEO, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet and SQL Server Magazines
Author of SQL 2005 DBCC CHECKDB/repair code

helpme
Posting Yak Master

141 Posts

Posted - 2010-05-20 : 14:27:30

this is what i get

-----------------------------------

DBCC results for 'sys.sysallocunits'.
There are 641 rows in 10 pages for object "sys.sysallocunits".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

helpme
Posting Yak Master

141 Posts

Posted - 2010-05-25 : 13:19:12

any more thoughts here, Paul?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-25 : 13:34:35

I believe Paul is on vacation. If this is something that you need to get answered soon, you should open a case with Microsoft PSS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-25 : 15:44:00

Paul is on vacation, totally out of contact for around 2 weeks.

My (much less educated) opinion is that you are out of luck here. Did you have any luck locating a clean backup? Any evidence of manual changes to the system tables? (if they were modified in SQL 2005, there'll be a error log entry saying so)

--
Gail Shaw
SQL Server MVP

helpme
Posting Yak Master

141 Posts

Posted - 2010-05-26 : 08:23:42

Thanks Tara and Gail. They have a clean backup, unfortunely it is about 2 weeks old. I looked in the error log after the last good backup (I'm assuming it would have happened after that) and there are just 'login failed' messages, nothing about the system tables being changed.

If I manually create another database, pull the ddl from the bad database, create the tables (non-system) in the new database and pull the data into the new database from the old database, would something like this work? Not exactly what I want to do, but as you say, my options are plenty limited. Thanks for any guidance you can give.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources