July 14, 2011 at 2:45 am
Hi,
I have a database in sql2000 which i just moved in to a 2008 test environment. I ran DBCC CHECKALLOC and found that it has some allocation errors related to IAM page which looks corrupt. Someone might have changed some system objects long back and corrupted it.
following is the error message i am getting:
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:582844) in object ID 0, index ID -1, partition ID 0, alloc unit ID 343941174853632 (type Unknown), 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:502784) in object ID 0, index ID -1, partition ID 0, alloc unit ID 358621291151360 (type Unknown), but it was not detected in the scan.
CHECKALLOC found 2 allocation errors and 0 consistency errors not associated with any single object.
Someone please show me some light. I tried repair_fast and repair_rebuild options as they do not cause any data loss but with no luck. Repair_allow_data_loss may cause some loss of data pages for me and I am trying to avoid that.
Regards
Chandan
July 14, 2011 at 2:48 am
Please run the following and post the full results
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
July 14, 2011 at 3:52 am
GilaMonster (7/14/2011)
Please run the following and post the full results
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Please find the dbcc results:
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:582844) in object ID 0, index ID -1, partition ID 0, alloc unit ID 343941174853632 (type Unknown), 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:502784) in object ID 0, index ID -1, partition ID 0, alloc unit ID 358621291151360 (type Unknown), but it was not detected in the scan.
CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 2 allocation errors and 0 consistency errors in database 'DB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DB).
Thank you!
July 14, 2011 at 4:54 am
there was another amazing thing I discovered here.Just for my fun sake, i created a maintenance plan to rebuild all the indexes and rather than using the wizard to do this, i copied the T-SQL script generated by this wizard and executed them one by one till i found something nasty:
There were scripts generated for index rebuilds on 4 tables which do not exists in reality:-P
Since the errors posted above shows some corruption in Index Allocation Map page, i am trying the following
- Begin Tran
- Note down the rowcounts for all the tables(including system objects)
- Run checkdb with repair option
- ensure that error has gone and IAM allocation error is fixed
-run a clean checkdb to make sure it is good
- observe the rowcounts again
- commit if i am satisfied
-rollback the transaction to bring the db to original state
I am hoping that someone is looking at this post 🙂
July 14, 2011 at 5:18 am
chandan_jha18 (7/14/2011)
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DB).
There's your answer right there.
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
July 14, 2011 at 5:36 am
GilaMonster (7/14/2011)
chandan_jha18 (7/14/2011)
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DB).There's your answer right there.
I tried that and now i found that in both the database versions, the row counts are the same(including system objects). I am trying to compare the data now.
Just wondering if actually there was loss of some data.
BTW, did you find the weird thing above? Index rebuild tasks scripted out tables that do not exist.
July 14, 2011 at 5:48 am
Fixing that error requires Allow_data_loss because of the types of repairs. If those are the only errors, then it can fix without actually discarding anything
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
July 14, 2011 at 5:58 am
GilaMonster (7/14/2011)
Fixing that error requires Allow_data_loss because of the types of repairs. If those are the only errors, then it can fix without actually discarding anything
thank you Gail. while running repair, i chose to see the information messages as well. It was written that a number of page deallocations did happen.
I am using the Red Gate tool- Data compare to compare the database between the two database versions.
I hope it comes good.Also, i am planning not to fix anything in 2000 version but after migration to 2008 version only. Sounds better to you?
Thanks
Chandan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply