6.5 (YES...6.5) corruption - 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

General SQL Server Forums

Data Corruption Issues

6.5 (YES...6.5) corruption

Author

Topic

mddba
Starting Member

4 Posts

Posted - 2005-11-02 : 15:44:26

A rouge 6.5 DB was brought to my attention a few weeks ago. We can't upgrade until we solve some corruption issues. We're receiving the listed message after running a DBCC NEWALLOC. We're not sure but we believe the corruption is with a primary key. We can't drop the primary key without of course, first dropping all the foreign keys. ALLOW_DATA_LOSS wasn't introduced until 7.0. Rebuilding the indexes doesn't fix the problem. Does anyone have any suggestion on how to fix this corruption? Any help is appreciated. Restoring isn't an option as this corruption exists in the oldest backup we have...
thanks in advance
************
TABLE: ABS_PAT_INSURANCES OBJID = 204527762
INDID=1 FIRST=4041968 ROOT=4039657 DPAGES=14304 SORT=0
Msg 2525, Level 16, State 1
Table Corrupt: Object id wrong; tables: alloc page 4036864 extent id=4036880 l page#=4036880 objid in ext=204527762 (name = ABS_PAT_INSURANCES) objid in page=348528275 (name = ABS_PAT_MAIN)objid in sysindexes=204527762 (name = ABS_PAT_INSURANCES)
Data level: 1. 0 Data Pages in 1789 extents.
INDID=2 FIRST=3926064 ROOT=3917574 DPAGES=4305 SORT=1
Msg 2525, Level 16, State 1
Table Corrupt: Object id wrong; tables: alloc page 3926016 extent id=3926064 l page#=3926064 objid in ext=204527762 (name = ABS_PAT_INSURANCES) objid in page=348528275 (name = ABS_PAT_MAIN)objid in sysindexes=204527762 (name = ABS_PAT_INSURANCES)
TOTAL # of extents = 1789

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-02 : 16:30:40

Here's an answer from some buddies in PSS - hope this helps:

6.5 was mostly about copy data out, drop object and move on. Not much to repairing the extent to the object mismatch. Be careful because normal drop of the object can often drop other objects because it runs chains and does not check ids in most cases. If you can deref the object from sysindexes, syscolumns and sysobjects you can repair the allocation problems. FKs are going to kick you in the pants a bit.

Fastest Way

Rebuild extents might work if you cross your fingers as long as page chains are okay
If not try to use the old database transfer wizard and suck out as much as possible and move on

Other way

Copy out the data (BCP usually the best) or select INFO another DB, not same one or allocation corruption can lead to more if you know what I mean.
Script out the schema for the table
Hand drop the object
dbcc rebuildextents – I think was the command
Reload the object

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-11-02 : 16:31:33

Man, the memories of these.

Can you BCP the data out of the existing ABS_PAT_INSURANCES table? Or select * into a new table? If you can, you can try to create a new copy of the table, then do the sp_rename switch. This will naturally require re-doing the foreign keys, but it looks like you wil be stuck with that, anyway.

mddba
Starting Member

4 Posts

Posted - 2005-11-02 : 16:49:31

Thanks for the tips.

I don't think extracting the data is an option. A select * errors out after a few rows and select count(*) returns:

Msg 605, Level 21, State 1
Attempt to fetch logical page 3924872 in database 'datarepo' belongs to object 'ABS_PAT_MAIN', not to object 'ABS_PAT_INSURANCES'.
The SQL Server is terminating this process.

Please don't tell me this table is toast...

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-02 : 17:22:19

Yes, looks like its toast. Can you extract up to the broken point and then from that point afterwards (i.e. by working out what key values are on the broken page and working around them?)

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

mddba
Starting Member

4 Posts

Posted - 2005-11-03 : 15:59:54

toast...never liked that word...Any recommendations on how to find the foreign keys that connect to these tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources