August 11, 2010 at 3:35 pm
A customer has sent me this error returned by DBCC checkdb on their sql 2000 server:
Server: Msg 8904, Level 16, State 1, Line 1 Extent (1:305920) in database ID 8 is allocated by more than one allocation object.
Server: Msg 8913, Level 16, State 1, Line 1 Extent (1:305920) is allocated to 'sysindexes, (255)Text' and at least one other object.
Server: Msg 2533, Level 16, State 1, Line 1 Table error: Page (1:305921) allocated to object ID 2, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 2533, Level 16, State 1, Line 1 Table error: Page (1:305922) allocated to object ID 2, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 2533, Level 16, State 1, Line 1 Table error: Page (1:305923) allocated to object ID 2, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 2533, Level 16, State 1, Line 1 Table error: Page (1:305924) allocated to object ID 2, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 2533, Level 16, State 1, Line 1 Table error: Page (1:305925) allocated to object ID 2, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 2533, Level 16, State 1, Line 1 Table error: Page (1:305926) allocated to object ID 2, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 2533, Level 16, State 1, Line 1 Table error: Page (1:305927) allocated to object ID 2, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 2533, Level 16, State 1, Line 1 Table error: Page (1:305920) allocated to object ID 60279720, index ID 0 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 8913, Level 16, State 1, Line 1 Extent (1:305920) is allocated to 'TableX' and at least one other object.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 7 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 1 allocation errors and 1 consistency errors in table 'TableX' (object ID 60279720).
CHECKDB found 3 allocation errors and 8 consistency errors in database 'DatabaseX'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DATABASEX ).
Unfortunately, this has been happening since the beginning of May. So the earliest good backup they have is from May 1st. I restored a copy of a bad backup with the corruption. I tried rebuilding the indexes on 'TableX' and dbcc checkDb with REPAIR_ALLOW_DATA_LOSS.
Any ideas would be greatly appreciated.
Thanks.
Mike
August 11, 2010 at 10:27 pm
These are errors in the statistics blobs for the database EPICSYS (some of their pages are marked as allocated in two IAM pages for two objects) and you're not going to be able to fix this with CHECKDB. From what you've said about your backups, your choices are:
1) use DBCC PAGE to try to figure out which tables in the database are affected and drop their statistics
2) extract all data out into a new database
Unless you're really comfortable using DBCC PAGE I would recommend option #2.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
August 11, 2010 at 11:44 pm
If you've got time (maint window) and it's not a big database, you could script and drop all indexes and stats, then recreate all the indexes. Lots of work, make sure no one's using the DB while pks and constraints are off.
Otherwise, see above.
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
August 12, 2010 at 5:16 am
Thank you for the advice. Since I am not really comfortable using DBCC PAGE (although it sounds fun to play with on the backup) I'll start with testing extracting out the data to a new database or drop all indexes and stats and then rebuilding the indexes. The database is only around 4 GB and I can have them schedule maint. to allow for enough downtime.
Mike Reigler
August 18, 2010 at 1:21 pm
Just a quick thanks. We were able to recover by extracting all the data into a new database.
Mike
August 18, 2010 at 1:29 pm
Glad to hear. Tedious to do, but at least you didn't lose a couple month's data.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply