DBCC CHECKDB errors - SQL Server Forums

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.

All Forums

General SQL Server Forums

Data Corruption Issues

DBCC CHECKDB errors

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 2005

Msg 8992, Level 16, State 1, Line 4
Check 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 4
Check 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 4
Check 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 4
Check 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

Posted - 2009-02-09 : 15:21:07

Moving this to the data corruption forum. Pleae post the entire output of DBCC CHECKDB for that database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

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 4
Check 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 4
Check 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 4
Check 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 4
Check 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]

Thanks

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & 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 Shaw
SQL 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.objects
where object_id in (427356787 , 811358155, 827358212, 1016598910, 933122615)

object_id name type_desc
----------- ----------------------------------- ---------------------
427356787 fnIMGetSerialIncrMask SQL_SCALAR_FUNCTION
811358155 fnIMSetSerialNoIncrmntValue SQL_SCALAR_FUNCTION
827358212 fnIMGetSerialNoIncrmntValue SQL_SCALAR_FUNCTION
933122615 spwaITGenerateIncSerial SQL_STORED_PROCEDURE
1016598910 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 advice
Try dropping and recreating those functions and procedures

Dropping them might (and I emphasis might) drop the bad records out of sys.depends. Or the drop may fail because of the errors.

--
Gail Shaw
SQL 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 Dickson
Texas

P.S.
I forgot to mention what exactly worked. Dropping and recreating the functions and procedures worked. I can run a CHECKDB now without errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources