January 22, 2011 at 1:41 pm
Hi,
I migrated a database created in SQL 2000 to SQL 2005 with an Access application front end and started encountering the dbcc error below.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3854, State 1: Attribute (parent_object_id=18099105) of row (object_id=34099162) in sys.objects has a matching row (object_id=18099105) in sys.objects (type=S ) that is invalid.
After running SELECT object_id, name, type_desc FROM sys.objects WHERE object_id in (34099162, 18099105) I got the following details:
object_id name type_desc
----------- ------------------------ ----------------------
18099105 syskeys SYSTEM_TABLE
34099162 UQ__syskeys__02084FDA UNIQUE_CONSTRAINT
Thanks for your help.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
January 23, 2011 at 1:54 am
Do you still have the SQL 2000 DB?
I have a feeling that this requires script, export, recreate to fix.
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
January 24, 2011 at 10:27 am
GilaMonster (1/23/2011)
Do you still have the SQL 2000 DB?I have a feeling that this requires script, export, recreate to fix.
Yes I do. It was detached from the 2000 server because of the migration to SQL 2005 but I still have it.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
January 24, 2011 at 11:01 am
Attach it to a SQL 2000 instance (NB SQL 2000!) and run DBCC CheckCatalog
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
January 24, 2011 at 1:13 pm
DBCC CHECKCATALOG ran successfully.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
January 24, 2011 at 1:32 pm
Odd...
I seem to recall a thread on exactly this error a while back. Maybe search sround, see if you can find it. I can't remember what the final recommendation was for the OP in that thread, but I suspect this is going to require the script, export, recreate 'fix'
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
January 24, 2011 at 2:14 pm
Is there a reason why DBCC doesnt catch this in SQL 2000?
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
January 24, 2011 at 2:23 pm
CHECKCATALOG on 2000 was very basic - that's why it was rewritten and I included it in CHECKDB.
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
January 24, 2011 at 2:45 pm
Thanks.
How worrisome should I be about this error? Why I can attempt to fix this now by recreating the DB and exporting the data in Test, the fix will not be migrated to production until our next code release which could be couple of months.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
January 24, 2011 at 3:38 pm
Could you please run DBCC CHECKDB and see is there any error on the report?
January 24, 2011 at 3:45 pm
Script out the table in SSMS and recreate it with a new name, migrate data, delete old table, rename new table to old name.
I got this error quite a few times when I migrated from 2000 -> 2005 and it ended up being faster just to recreate the table than it was to try and troubleshoot cryptic objectid errors.
Also, you defrag the table in the process 😀
January 24, 2011 at 4:01 pm
Derrick Smith (1/24/2011)
Script out the table in SSMS and recreate it with a new name, migrate data, delete old table, rename new table to old name.
It's a system table. (or at least it claims to be a system table)
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
January 24, 2011 at 4:02 pm
moosamca (1/24/2011)
Could you please run DBCC CHECKDB and see is there any error on the report?
The checkDB error is listed in the first post:
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3854, State 1: Attribute (parent_object_id=18099105) of row (object_id=34099162) in sys.objects has a matching row (object_id=18099105) in sys.objects (type=S ) that is invalid.
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
January 24, 2011 at 4:05 pm
Hi,
Did you tried DBCC CHECKTable with Repair_rebuild?
Regards
Moosa
January 24, 2011 at 4:07 pm
moosamca (1/24/2011)
Did you tried DBCC CHECKTable with Repair_rebuild?
Waste of time. It's a catalog error. Repair_rebuild repairs damage to nonclustered indexes. Nothing, not even repair_allow_data_loss fixes schema corruption.
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 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply