Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
chip
Starting Member
14 Posts |
Posted - 2009-02-09 : 15:01:08
|
I know this has been talked about before, but I have not seen a complete explaination of how to resolve this other than a drop-rebuild. I am a little confused about dropping what and rebuilding what? Database? Tables? Stored procedured? How do I know? Database is way too big to manually rebuild and if I script it, won't that recreate the problem? SQL 2005Msg 8992, Level 16, State 1, Line 4Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=427356787,referenced_minor_id=1) of row (class=0,object_id=1016598910,column_id=0,referenced_major_id=427356787,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=427356787,column_id=1) in sys.columns.Msg 8992, Level 16, State 1, Line 4Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=427356787,referenced_minor_id=1) of row (class=0,object_id=933122615,column_id=0,referenced_major_id=427356787,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=427356787,column_id=1) in sys.columns.Msg 8992, Level 16, State 1, Line 4Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=811358155,referenced_minor_id=1) of row (class=0,object_id=933122615,column_id=0,referenced_major_id=811358155,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=811358155,column_id=1) in sys.columns.Msg 8992, Level 16, State 1, Line 4Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=827358212,referenced_minor_id=1) of row (class=0,object_id=933122615,column_id=0,referenced_major_id=827358212,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=827358212,column_id=1) in sys.columns.Would love some help with this. Thanks! |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
chip
Starting Member
14 Posts |
Posted - 2009-02-09 : 15:25:50
|
Here is the complete DBCC CHECKDB output:Msg 8992, Level 16, State 1, Line 4Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=427356787,referenced_minor_id=1) of row (class=0,object_id=1016598910,column_id=0,referenced_major_id=427356787,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=427356787,column_id=1) in sys.columns.Msg 8992, Level 16, State 1, Line 4Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=427356787,referenced_minor_id=1) of row (class=0,object_id=933122615,column_id=0,referenced_major_id=427356787,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=427356787,column_id=1) in sys.columns.Msg 8992, Level 16, State 1, Line 4Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=811358155,referenced_minor_id=1) of row (class=0,object_id=933122615,column_id=0,referenced_major_id=811358155,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=811358155,column_id=1) in sys.columns.Msg 8992, Level 16, State 1, Line 4Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=827358212,referenced_minor_id=1) of row (class=0,object_id=933122615,column_id=0,referenced_major_id=827358212,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=827358212,column_id=1) in sys.columns.CHECKDB found 0 allocation errors and 4 consistency errors not associated with any single object.CHECKDB found 0 allocation errors and 4 consistency errors in database 'mas500_app'. |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2009-02-10 : 09:49:16
|
You have two choices - neither of which you're going to like. These errors cannot be repaired by DBCC CHECKDB, so you can either go back to 2000 and fix them there by manually hacking the system tables, or you can manually hack the system tables on 2005. There's absolutely no other choice except to extract all your data into a new database - Product Support won't tell you any different.My guess as to the cause is that someone manually changed a system table in 2000 and didn't remove the entries from syscolumns.If you want to try hacking the system tables in 2005, here's what to do [url]http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Using-the-SQL-2005-Dedicated-Admin-Connection-to-fix-Msg-8992-corrupt-system-tables.aspx[/url]ThanksPaul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)SQL Server MVP, Contributing Editor of TechNet MagazineAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database course |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-02-10 : 10:31:37
|
If you use objectname to see what objects those objectIDs are, what are they? Tables, views, stored procedures, etc?--Gail ShawSQL Server MVP |
|
|
chip
Starting Member
14 Posts |
Posted - 2009-02-10 : 14:16:24
|
GilaMonster, I hope this is what you were asking for:select object_id, name, type_desc from sys.objectswhere object_id in (427356787 , 811358155, 827358212, 1016598910, 933122615)object_id name type_desc----------- ----------------------------------- ---------------------427356787 fnIMGetSerialIncrMask SQL_SCALAR_FUNCTION811358155 fnIMSetSerialNoIncrmntValue SQL_SCALAR_FUNCTION827358212 fnIMGetSerialNoIncrmntValue SQL_SCALAR_FUNCTION933122615 spwaITGenerateIncSerial SQL_STORED_PROCEDURE1016598910 spwaITCheck4ValidMask SQL_STORED_PROCEDURE |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-02-10 : 15:35:12
|
I make absolutely no guarantee this will fix anything. It probably won't. If it doesn't, see Paul's adviceTry dropping and recreating those functions and proceduresDropping them might (and I emphasis might) drop the bad records out of sys.depends. Or the drop may fail because of the errors.--Gail ShawSQL Server MVP |
|
|
chip
Starting Member
14 Posts |
Posted - 2009-02-10 : 15:54:08
|
AWESOME, FANTASTIC, HALLELUIAH, THAT WORKED!!!!Thank you, thank you, thank you very much!!!Chip DicksonTexasP.S.I forgot to mention what exactly worked. Dropping and recreating the functions and procedures worked. I can run a CHECKDB now without errors. |
|
|
|
|
|
|
|