Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extent allocated to more than on object Expand / Collapse
Author
Message
Posted Wednesday, August 11, 2010 3:35 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:35 AM
Points: 98, Visits: 123
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
Post #967852
Posted Wednesday, August 11, 2010 10:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 4:00 PM
Points: 2,038, Visits: 1,663
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
Post #967926
Posted Wednesday, August 11, 2010 11:44 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #967942
Posted Thursday, August 12, 2010 5:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:35 AM
Points: 98, Visits: 123
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

Post #968072
Posted Wednesday, August 18, 2010 1:21 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:35 AM
Points: 98, Visits: 123
Just a quick thanks. We were able to recover by extracting all the data into a new database.

Mike
Post #971407
Posted Wednesday, August 18, 2010 1:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #971419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse