December 11, 2011 at 10:46 am
Ok... this looks like real fun. Not.
For the record, what I'm about to tell you to do is extremely risky. It could easily cause further damage and is not in any way guaranteed to work. Try this out on a copy of the live database (on SQL 2000) and see what happens.
This is specifically and only for this problem. Anyone running across this thread from a search engine, this is NOT a general fix.
Steps:
Turn allow updates on (sp_configure 'allow updates',1)
run the reconfigure statement
Delete from sysreferences where rkeyid IN (27147142, 964914509, 1778821399, 1904061869, 2092586543) AND rkeyindid = 2
This should affect 6 rows. If it does not, discard the DB and restore a fresh backup from live.
It it has affected 6 rows only, run DBCC CheckCatalog (SQL 2000). If that throws any errors, post them here.
If there are no errors, take a backup of the 'repaired' database and attempt to restore to SQL 2008. Run CheckDB there. If it throws any errors, post them here.
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
December 12, 2011 at 3:07 pm
Hi Gail--
Okay, thanks. So, I ran the update on the 2k db, and when I ran dbcc checkcatalog, it returned no errors. So far so good.
I then restored the DB onto 2008 and ran dbcc checkcatalog and got the errors below. progress?
Msg 3852, Level 16, State 1, Line 1
Row (object_id=73103351) in sys.foreign_keys does not have a matching row (constraint_object_id=73103351,constraint_column_id=1) in sys.foreign_key_columns (referenced_column_id=NULL).
Msg 3852, Level 16, State 1, Line 1
Row (object_id=73103351) in sys.foreign_keys does not have a matching row (constraint_object_id=73103351,constraint_column_id=1) in sys.foreign_key_columns (parent_column_id=NULL).
Msg 3857, Level 16, State 1, Line 1
The attribute (referenced_object_id=NULL,key_index_id=NULL) is required but is missing for row (object_id=73103351) in sys.foreign_keys.
Msg 3852, Level 16, State 1, Line 1
Row (object_id=686989874) in sys.foreign_keys does not have a matching row (constraint_object_id=686989874,constraint_column_id=1) in sys.foreign_key_columns (referenced_column_id=NULL).
Msg 3852, Level 16, State 1, Line 1
Row (object_id=686989874) in sys.foreign_keys does not have a matching row (constraint_object_id=686989874,constraint_column_id=1) in sys.foreign_key_columns (parent_column_id=NULL).
Msg 3857, Level 16, State 1, Line 1
The attribute (referenced_object_id=NULL,key_index_id=NULL) is required but is missing for row (object_id=686989874) in sys.foreign_keys.
Msg 3853, Level 16, State 1, Line 1
Attribute (referenced_object_id=748581755,key_index_id=2) of row (object_id=828582040) in sys.foreign_keys does not have a matching row (object_id=748581755,index_id=2) in sys.indexes.
Msg 3853, Level 16, State 1, Line 1
Attribute (referenced_object_id=748581755,key_index_id=2) of row (object_id=844582097) in sys.foreign_keys does not have a matching row (object_id=748581755,index_id=2) in sys.indexes.
Msg 3853, Level 16, State 1, Line 1
Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=506133244) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes.
Msg 3853, Level 16, State 1, Line 1
Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=522133301) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes.
Msg 3853, Level 16, State 1, Line 1
Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=1010154694) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes.
Msg 3853, Level 16, State 1, Line 1
Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=1042154808) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes.
Msg 3853, Level 16, State 1, Line 1
Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=1723921263) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes.
Msg 3852, Level 16, State 1, Line 1
Row (object_id=1052582838) in sys.foreign_keys does not have a matching row (constraint_object_id=1052582838,constraint_column_id=1) in sys.foreign_key_columns (referenced_column_id=NULL).
Msg 3852, Level 16, State 1, Line 1
Row (object_id=1052582838) in sys.foreign_keys does not have a matching row (constraint_object_id=1052582838,constraint_column_id=1) in sys.foreign_key_columns (parent_column_id=NULL).
Msg 3857, Level 16, State 1, Line 1
The attribute (referenced_object_id=NULL,key_index_id=NULL) is required but is missing for row (object_id=1052582838) in sys.foreign_keys.
Msg 3852, Level 16, State 1, Line 1
Row (object_id=1084582952) in sys.foreign_keys does not have a matching row (constraint_object_id=1084582952,constraint_column_id=1) in sys.foreign_key_columns (referenced_column_id=NULL).
Msg 3852, Level 16, State 1, Line 1
Row (object_id=1084582952) in sys.foreign_keys does not have a matching row (constraint_object_id=1084582952,constraint_column_id=1) in sys.foreign_key_columns (parent_column_id=NULL).
Msg 3857, Level 16, State 1, Line 1
The attribute (referenced_object_id=NULL,key_index_id=NULL) is required but is missing for row (object_id=1084582952) in sys.foreign_keys.
Msg 3852, Level 16, State 1, Line 1
Row (object_id=1739921320) in sys.foreign_keys does not have a matching row (constraint_object_id=1739921320,constraint_column_id=1) in sys.foreign_key_columns (referenced_column_id=NULL).
Msg 3852, Level 16, State 1, Line 1
Row (object_id=1739921320) in sys.foreign_keys does not have a matching row (constraint_object_id=1739921320,constraint_column_id=1) in sys.foreign_key_columns (parent_column_id=NULL).
Msg 3857, Level 16, State 1, Line 1
The attribute (referenced_object_id=NULL,key_index_id=NULL) is required but is missing for row (object_id=1739921320) in sys.foreign_keys.
Msg 3852, Level 16, State 1, Line 1
Row (object_id=1936061983) in sys.foreign_keys does not have a matching row (constraint_object_id=1936061983,constraint_column_id=1) in sys.foreign_key_columns (referenced_column_id=NULL).
Msg 3852, Level 16, State 1, Line 1
Row (object_id=1936061983) in sys.foreign_keys does not have a matching row (constraint_object_id=1936061983,constraint_column_id=1) in sys.foreign_key_columns (parent_column_id=NULL).
Msg 3857, Level 16, State 1, Line 1
The attribute (referenced_object_id=NULL,key_index_id=NULL) is required but is missing for row (object_id=1936061983) in sys.foreign_keys.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
December 12, 2011 at 3:37 pm
What about rescripting the database from scratch, then DTS the data into the empty shell? I've read on a few different posts that this is a possible solution. Would be nice to fix the system tables directly, but if that isn't possible, would you recommend this route?
December 13, 2011 at 1:53 am
jacobbdrew (12/12/2011)
What about rescripting the database from scratch, then DTS the data into the empty shell? I've read on a few different posts that this is a possible solution. Would be nice to fix the system tables directly, but if that isn't possible, would you recommend this route?
I think we're going to have to do that. I was hoping it was a simple removal of some orphaned rows, but given that list of errors, we could be cleaning out the system tables for days.
Do that on SQL 2000, recreate the DB on SQL 2000 and then check that it's clean all the way up.
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
December 13, 2011 at 3:19 am
Bummer. Any particular reason I should do this on 2000? I ask because I can work locally on 2008, but I have to work remotely on 2000...
December 13, 2011 at 3:39 am
jacobbdrew (12/13/2011)
Bummer. Any particular reason I should do this on 2000? I ask because I can work locally on 2008, but I have to work remotely on 2000...
If you really want some fun please do it. SS2K & SS2K8 migration has its own issues / complexities. Would you like it to add in your case?
IMO you should restore your DB on SS2K successfully. Once done you may think of SS2K8 upgrade later.
December 13, 2011 at 3:51 am
jacobbdrew (12/13/2011)
Bummer. Any particular reason I should do this on 2000?
Simplicity. It'll likely work fine on 2008, but with schema corruption like this it's probably safer to keep things on one version rather than complicate matters by trying to fix and upgrade in one go.
Other reason is so that you can replace the current prod database with the fixed one and test it on the current system to make sure nothing's been missed
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
December 13, 2011 at 6:33 pm
so, I'm going to try this tonight and have been trying to research the best options. here's what i have so far (with some questions.)
step 1) create scripts for the tables structures, relationships, constraints etc...
-The way I do this is through the right-click option in enterprise manager, but if there's a better way, I'm all ears.
-Should I script the keys and constraints in this step? Seems like they are the problem, so wouldn't i be scripting the problem as well?
step 2) DTS from old DB to new DB
-Again, I use the wizard for this, but if you'd recommend a better way...
-I should try and just copy the data here, yes? Not objects. How is this functionally different than scripting the tables in the step above?
Sorry if this seems excessive. I'm like the dumb questions.
December 14, 2011 at 1:14 am
jacobbdrew (12/13/2011)
so, I'm going to try this tonight and have been trying to research the best options. here's what i have so far (with some questions.)step 1) create scripts for the tables structures, relationships, constraints etc...
-The way I do this is through the right-click option in enterprise manager, but if there's a better way, I'm all ears.
-Should I script the keys and constraints in this step? Seems like they are the problem, so wouldn't i be scripting the problem as well?
Script everything. The foreign key constraints may fail, which means you'll have to work through them one by one, but there is no way from an ALTER TABLE .. ADD CONSTRAINT statement to create the orphaned mess that you have. So if the constraint scripts, it'll work fine.
Don't forget the views, procedures, functions, users, triggers, indexes, defaults, etc. Everything must be scripted. You're going to use that to create a new database that looks like the old one, then drop the old one, so everything must come across
step 2) DTS from old DB to new DB
-Again, I use the wizard for this, but if you'd recommend a better way...
-I should try and just copy the data here, yes? Not objects. How is this functionally different than scripting the tables in the step above?
DTS, bcp out/bcp in. Whatever you're comfortable with.
Not sure I understand your last question. Step 1 is creating the structure of the database (tables, views, procedures, etc). Step 2 is populating them with 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
December 14, 2011 at 1:36 am
to ease your load, you could consider creating FK-constraints after the load has been performed.
But keep in mind to put them back in place.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 14, 2011 at 1:47 am
ALZDBA (12/14/2011)
to ease your load, you could consider creating FK-constraints after the load has been performed.
And probably the nonclustered indexes as well.
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
December 28, 2011 at 11:46 am
Thanks for all your help with this. I was able rebuild the schema then import data. The data import failed where there were some orphaned rows and I was able to locate and fix them pretty easily.
-Jacob
December 30, 2011 at 12:59 pm
After moving or restoring database from 2000.
use dbcc updateusage and then execute Dbcc CheckDb
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply