December 3, 2008 at 3:45 pm
Hey Perry,
How will you query the system view to find out which foreign key is effected? What I did was dropped the foreign key that was available with the select * from sys.foreign_keys ( which i had scripted before dropping ). Ran the DBCC checkDb , got the same inconsistency error... ?? I did not though to check which key is affected. can you please tell me how to query the system view?
December 3, 2008 at 4:01 pm
You have the object IDs in the error message. Use those with object_name() to find the objects and then perhaps drop and rebuild them.
I would also dig in and check all hardware. Corruption errors are most often hardware related, so be sure that your drivers and hardware is not causing issues.
December 4, 2008 at 12:28 am
I don't think that dropping the keys is going to get rid of all the messages. Some, maybe.
As well as foreign keys with missing key column info (which dropping the keys will fix) you've got key columns without a foreign key. Since you don't have a foreign key to drop, those records will stay and keep throwing errors.
Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=1874157772,key_index_id=2) exists without a row (object_id=1938158000) in sys.foreign_keys.
If you run select * from sys.foreign keys, how many rows are there?
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 4, 2008 at 12:54 am
Steve Jones - Editor (12/3/2008)
Corruption errors are most often hardware related, so be sure that your drivers and hardware is not causing issues.
This particular one's usually the result of this kinda thing on SQL 2000
sp_configure 'allow_updates',1
delete from sys...
Since SQL 2000 didn't run check catalogue with check db, the problems went unnoticed. 2005 does run check catalogue and so all the previously hidden problems show 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 4, 2008 at 1:33 am
srawant (12/2/2008)
Hey Gail,I have a same database in one of my pre-prod server which is currently dead. I can work on this server to fix the database. So, can you tell me procedure..
If the dropping of the foreign keys doesn't fix it, then you need to migrate the contents of the database elsewhere.
Script all of the objects in the database (this can be done from management studio, object explorer, right click database - generate scripts), apply those scripts in a new DB, then copy all of the data over. SSIS or the import/export wizard can do that.
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 4, 2008 at 6:52 am
The *only* way to fix this in the current database is to go in and hack the system tables using the procedure I've documented.
You could migrate to a new database straight away, or you could simply live with these errors for a while and migrate when you have more time.
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
April 15, 2010 at 4:03 am
The solution I found was to create a list of each of the objects in sys.foreign_keys and sys.indexes that it said it couldn't. In the object browser, I then right clicked the object in question, chose the script object as option and used the drop and create to to create to a new query editor window. I then ran the script.
This resolved my issue.
April 15, 2010 at 4:19 am
That will work for certain, specific types of schema corruption, it may not work for this one, as I commended several posts back.
p.s. You did notice that this thread is 2 years old?
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 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply