May 15, 2009 at 3:27 pm
Hello All,
I have a DB that running fine on SQL2000 sp3a and it is getting migrated to a 2005 sp3 server. After the successful restore, maintenance fails. These are the errors I get from DBCC;
========================
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=16055143,referenced_minor_id=8) of row (class=0,object_id=16823222,column_id=0,referenced_major_id=16055143,referenced_minor_id=8) in sys.sql_dependencies does not have a matching row (object_id=16055143,column_id=8) in sys.columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=16055143,referenced_minor_id=8) of row (class=0,object_id=32823279,column_id=0,referenced_major_id=16055143,referenced_minor_id=8) in sys.sql_dependencies does not have a matching row (object_id=16055143,column_id=8) in sys.columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=16055143,referenced_minor_id=8) of row (class=0,object_id=48823336,column_id=0,referenced_major_id=16055143,referenced_minor_id=8) in sys.sql_dependencies does not have a matching row (object_id=16055143,column_id=8) in sys.columns.
CHECKDB found 0 allocation errors and 3 consistency errors not associated with any single object.
========================
I checked the books on line and found the error message. The results said this;
========================
User Action
Drop and Re-create the Specified Object
If possible, drop and recreate the specified object. For example, if the object is a stored procedure or user-defined type, recreating the object may resolve the problem.
Restore from Backup
If the problem is not hardware related and a known clean backup is available, restore the database from the backup. This action is only applicable if the backup does not contain the metadata error.
Run DBCC CHECKDB
Not applicable. This error cannot be repaired. If you cannot restore the database from a backup, contact Microsoft Customer Service and Support (CSS).
========================
So how can I find out which objects are corrupt? I want to see if I can drop and recreate them, but I am not sure how to identify them. That really seems to be my only option, as this is a fresh restore.
Thanks for any help/advise you can give.
Monty
May 15, 2009 at 3:35 pm
Use the object_name function.
So, to get all the objects involved, you would run this
SELECT Object_Name(16823222)
SELECT Object_Name(16055143)
SELECT Object_Name(32823279)
SELECT Object_Name(48823336)
The reason you weren't getting these on SQL 2000 is that CheckDB there did not run checkCatalog. The errors may very well have been around for some time.
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
May 15, 2009 at 4:01 pm
Thank you for the fast help!
I ran the query in the database that has the problem and got this result back;
SELECT Object_Name(16823222)
go
U_updateBOUserPhoneNumber
SELECT Object_Name(16055143)
go
U_Phone_Number
SELECT Object_Name(32823279)
go
U_updateSitePhoneNumber
SELECT Object_Name(48823336)
go
U_updateUserPhoneNumber
May 15, 2009 at 4:06 pm
Are those procs? If so, script them, drop them and then recreate them.
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
May 15, 2009 at 4:07 pm
As background, someone manually changed the system tables on the database before you upgraded.
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
May 15, 2009 at 4:10 pm
Paul Randal (5/15/2009)
As background, someone manually changed the system tables on the database before you upgraded.
Ugh... that is not good....
May 15, 2009 at 4:12 pm
GilaMonster (5/15/2009)
Are those procs? If so, script them, drop them and then recreate them.
Three of them are and I am doing that right now.
The one is a table...
May 15, 2009 at 4:50 pm
OK... final update!!!
I dropped and recreated the three SPs only. I then re-ran the maintenance to see if those three errors were gone. When the maintenance ran, it had a lot more errors. I basically seen that it said to run a DBCC updateusage command. I ran the command and then re-ran the maintenance.
To my surprise, everything is clean!!!!
Thank you for your help once again!!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply