Corrupt Table

Question

Corrupt Table

jacowess

Old Hand

Points: 353
More actions
January 18, 2009 at 10:45 am

#196292

Hi
I am trying to drop a corrupt table but get the error message "possible schema corruption, run DBCC CHECKCATALOG.
When running CHECKCATALOG I get the following -
Msg 3853, Level 16, State 1, Line 1
Attribute (parent_object_id=785854312,parent_column_id=1) of row (constraint_object_id=881854654,constraint_column_id=1) in sys.foreign_key_columns does not have a matching row (object_id=785854312,column_id=1) in sys.columns.
These objects does not exist in the schema anymore so it's trying to reference unknown old objects. This database was upgraded from 2000 a year or so ago so I assume the corruption came from then.
How can I remove this table? This is stopping DBCC CHECKDB from completing in my weekend maintenance.
I want to try and avoid editing the system tables directly if I can.
Many Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Unfortunately, with that error you only have two options.

1) Edit the system tables directly to remove the orphaned records (very tricky, as the system tables are very complex on SQL 2005) See - http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Using-the-SQL-2005-Dedicated-Admin-Connection-to-fix-Msg-8992-corrupt-system-tables.aspx

2) Script out the database, bcp out the data and recreate the database.

You can try dropping the foreign keys on that table and the ones referencing that table and recreate, but I doubt that's going to work.

The root cause is that someone hacked the system tables back on SQL 2000 (where it was relatively easy) and left orphaned records behind.

Is that the only error that checkDB's returning?

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

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

jacowess Old Hand Points: 353 More actions · Answer 2

Thanks Gail, I was afraid that those would be my only options. Yes that is the only errors I get back from checkdb.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

For what it's worth, if I had that error on a production database, I'd pick the second option if it was feasible (due to time constraints). Or you can just leave it. It's not harmful and shouldn't have any effects, other than causing checkDB to fail.

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

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

jacowess Old Hand Points: 353 More actions · Answer 4

Thanks, yes it's one of my production databases, it's not doing any harm like you said other than the annoyance of my maintenance always failing on checkdb.

I will look into step 2 as this is the route I will go if I decide to fix it.

Regards,

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

GilaMonster (1/18/2009)
For what it's worth, if I had that error on a production database, I'd pick the second option if it was feasible (due to time constraints). Or you can just leave it. It's not harmful and shouldn't have any effects, other than causing checkDB to fail.

Gail, this is not quite true - in order to get CHECKDB to perform all checks on an upgraded database you need to have the following completed successfully:

DBCC CHECKDB(db) WITH DATA_PURITY;

Until you can get the above to complete successfully, CHECKDB will not perform all checks that are possible. The above will not complete successfully until you fix these issues.

Review the topic in Books Online for further information.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yeah, true.

Even so, the only extra checks (afaik) that are added once checkdb with data purity has run successfully are the ones that you turn on by adding the WITH DATA_PURITY option. So even if it's never run successfully, and the data purity checks aren't included by default, you can still run them by adding that option.

Edit: I can drop a mail to Paul Randal to get a confirmation of that, if you like?

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

I don't think it is necessary for Paul to answer - I just wanted to note that using the data purity option invokes additional checks.

From the documentation, those checks will be performed by CHECKDB (without the data purity option) once you have a successful completion with the option turned on.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs