December 1, 2008 at 3:14 pm
Got this Error Why??
Executed as user: XXXXXXXX. ...bject_id=1874157772,key_index_id=2) exists without a row
(object_id=1938158000) in sys.foreign_keys. [SQLSTATE 42000] (Error 8992) Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=1874157772,referenced_column_id=1) exists without a row (constraint_object_id=1938158000,constraint_column_id=1) in sys.foreign_key_columns. [SQLSTATE 42000] (Error 8992) Check Catalog Msg 3855, State 1: Attribute (parent_object_id=1906157886,parent_column_id=2) exists without a row (constraint_object_id=1938158000,constraint_column_id=1) in sys.foreign_key_columns. [SQLSTATE 42000] (Error 8992) Check Catalog Msg 3853, State 1: Attribute (parent_object_id=1954158057,parent_column_id=2) of row (constraint_object_id=1986158171,constraint_column_id=1) in sys.foreign_key_columns does not have a matching row (object_id=1954158057,column_id=2) in sys.columns. [SQLSTATE 42000] (Error 8992) Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=19061... The step failed.
December 2, 2008 at 1:34 am
You've got database corruption.
Please run the following and post the results.
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
Do you have a backup? Do you have any idea when this started?
Was that DB upgraded from SQL 2000? It looks like someone did modifications to the system tables on 2000 and left orphaned records behind. SQL 2000 wouldn't notice, 2005 does.
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 2, 2008 at 9:45 am
Hi Gail,
Thanks for replying. As it is in the production, I will run it sometime later this week and will post the result.
December 2, 2008 at 10:27 am
Hi Gail,
I ran the integrity check with the DBCC checkdb with noinfomsgs,allerrormessage and the error is the same as i have mentioned above. Actually we already know the database has problems. We do have the backup but I guess its with the same corrupted database so will have the same error(inconsistency). What would be the possible solution for this?
December 2, 2008 at 11:09 am
Could you post the complete output of checkDB please, with all of its errors and recomendations.
Was this DB upgraded from SQL 2000 and how long ago was it upgraded?
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 2, 2008 at 11:24 am
Msg 8992, Level 16, State 1, Line 1
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.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=1874157772,referenced_column_id=1) exists without a row (constraint_object_id=1938158000,constraint_column_id=1) in sys.foreign_key_columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (parent_object_id=1906157886,parent_column_id=2) exists without a row (constraint_object_id=1938158000,constraint_column_id=1) in sys.foreign_key_columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (parent_object_id=1954158057,parent_column_id=2) of row (constraint_object_id=1986158171,constraint_column_id=1) in sys.foreign_key_columns does not have a matching row (object_id=1954158057,column_id=2) in sys.columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=1906157886,key_index_id=2) exists without a row (object_id=1986158171) in sys.foreign_keys.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=1906157886,referenced_column_id=1) exists without a row (constraint_object_id=1986158171,constraint_column_id=1) in sys.foreign_key_columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (parent_object_id=1954158057,parent_column_id=2) exists without a row (constraint_object_id=1986158171,constraint_column_id=1) in sys.foreign_key_columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (parent_object_id=2002158228,parent_column_id=2) of row (constraint_object_id=2034158342,constraint_column_id=1) in sys.foreign_key_columns does not have a matching row (object_id=2002158228,column_id=2) in sys.columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (parent_object_id=2002158228,parent_column_id=3) of row (constraint_object_id=2050158399,constraint_column_id=1) in sys.foreign_key_columns does not have a matching row (object_id=2002158228,column_id=3) in sys.columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=1906157886,key_index_id=2) exists without a row (object_id=2034158342) in sys.foreign_keys.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=1906157886,referenced_column_id=1) exists without a row (constraint_object_id=2034158342,constraint_column_id=1) in sys.foreign_key_columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (parent_object_id=2002158228,parent_column_id=2) exists without a row (constraint_object_id=2034158342,constraint_column_id=1) in sys.foreign_key_columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=1874157772,key_index_id=2) exists without a row (object_id=2050158399) in sys.foreign_keys.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (referenced_object_id=1874157772,referenced_column_id=1) exists without a row (constraint_object_id=2050158399,constraint_column_id=1) in sys.foreign_key_columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (parent_object_id=2002158228,parent_column_id=3) exists without a row (constraint_object_id=2050158399,constraint_column_id=1) in sys.foreign_key_columns.
CHECKDB found 0 allocation errors and 15 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 15 consistency errors in database 'XXXX'.
December 2, 2008 at 1:42 pm
This is saying that a bunch of foreign key constraints *partially* exist in the system catalogs.
Was the database upgraded from 2000? When was the last time DBCC CHECKDB ran without finding these errors - before or after the upgrade.
It's either a bug in 2005, or someone manually hacked the system tables (either in 2000 or 2005).
Without backups, this is going to be hard to get rid of - repair cannot fix this so your only options without backups are to migrate all schema and data to a new database, or to manually repair the 2005 system tables yourself using the method on my blog (not recommended).
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
December 2, 2008 at 2:16 pm
Paul Randal (12/2/2008)
Without backups, this is going to be hard to get rid of - repair cannot fix this so your only options without backups are to migrate all schema and data to a new database, or to manually repair the 2005 system tables yourself using the method on my blog (not recommended).
Personally I wouldn't be sure where to even start cleaning that out of the system tables without doing more damage in the process.
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 2, 2008 at 2:25 pm
Oh go on, be brave Gail! 😉
You can query the system catalogs using the DAC to figure out which of the object IDs referenced in the error messages are for actual tables and which are for broken bits of the constraints. Drop the broken bits of the constraints (if possible) and remove the leftover rows from the sys.foreign_keys and sys.foreign_key_columns until no more errors come back.
But, as I said, not recommended unless you really know what you're doing.
Cheers
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
December 2, 2008 at 2:28 pm
Hey Paul,
This was actually a database on 2000 later upgraded to k5. I do not have any remembarance when it ran successfully last time without errors. Now, we are trying to resolve it before it grows much bigger.. Can you suggest me how can I do all the migration of schema successfully creating a new database and how I can import the data because I do not have the backups that is errorless I guess....
Cheers
December 2, 2008 at 2:45 pm
Script all the objects (perhaps excluding the foreign keys) to files, bcp all of the data to file. Create a new database, run all of the script files, bcp all of the data in.
SSIS's transfer objects might also work, though I've never tried that.
If the new DB's on the same server, you could do a select ... into ... across the databases and then migrate the other objects by script.
How big's the DB in question?
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 2, 2008 at 2:47 pm
It is around 20 G
December 2, 2008 at 3:12 pm
That's going to take a while.
What's the availability requirements? Can you get a weekend where the DB isn't in use? It may not take that long, but just for safety...
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 2, 2008 at 3:24 pm
it sounds very much like there are foreign keys and they are missing column information.
i would query the system views and find out which foreign keys are affected. Drop them and then re run DBCC CHECKDB. If it comes up clean i would then re apply the foreign keys.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 2, 2008 at 5:19 pm
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..
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply