Consistency Error Problem - 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

Consistency Error Problem

Author

Topic

DanielDucharme
Starting Member

11 Posts

Posted - 2010-08-16 : 15:45:11

I ran DBCC CHECKDB on all of the companies databases as the beginning of a planed monthly maintenance I am putting into effect and I found several databases with consistancy errors. Now because most of these databases are stored offsite and are transfered in office only when we need to do some in house work on it, the only backup we have has those errors so I cannot restore from a backup. I tried using the REPAIR_ALLOW_DATA_LOSS option and that fixed 1 of the 4 databases with problems but did nothing for the other 3. Reading the MSDN I noticed that it works harder in emergency mode so I wrote and ran the following:

ALTER DATABASE [Charlestown] SET EMERGENCY, SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKDB ('Charlestown', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

GO

ALTER DATABASE [EastGreenwich] SET EMERGENCY, SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKDB ('EastGreenwich', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

GO

ALTER DATABASE [TrainingDB] SET EMERGENCY, SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKDB ('TrainingDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

GO

This gives me the following output:

Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (object_id=766832133) of row (class=0,object_id=766832133,column_id=0,referenced_major_id=212208552,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=766832133) in sys.objects.
Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=2005594929,referenced_minor_id=67108867) of row (class=0,object_id=1035267589,column_id=0,referenced_major_id=2005594929,referenced_minor_id=67108867) in sys.sql_dependencies does not have a matching row (object_id=2005594929,column_id=67108867) in sys.columns.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'Charlestown'.
Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (object_id=766832133) of row (class=0,object_id=766832133,column_id=0,referenced_major_id=212208552,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=766832133) in sys.objects.
Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=2005594929,referenced_minor_id=67108867) of row (class=0,object_id=1035267589,column_id=0,referenced_major_id=2005594929,referenced_minor_id=67108867) in sys.sql_dependencies does not have a matching row (object_id=2005594929,column_id=67108867) in sys.columns.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'EastGreenwich'.
Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (object_id=766832133) of row (class=0,object_id=766832133,column_id=0,referenced_major_id=212208552,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=766832133) in sys.objects.
Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=2005594929,referenced_minor_id=67108867) of row (class=0,object_id=1035267589,column_id=0,referenced_major_id=2005594929,referenced_minor_id=67108867) in sys.sql_dependencies does not have a matching row (object_id=2005594929,column_id=67108867) in sys.columns.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'TrainingDB'.

Now I am not sure what can be done to fix those errors. I am primarily a C++ programmer going for my PhD in Computer Science and specializing in Digital Forensics, but the company I work for needed someone to manage their databases so I am doing what I can. Any help would be appreciated and if you need any more information let me know.

Daniel Ducharme
Computer Systems Analyst

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-16 : 21:38:32

Would've been nice to see the output of CheckDB before you ran the repair_allow_data_loss...but...sounds like either views or stored procs reference tables that no longer exist.

Take the object_id's referenced and look at the definitions of those objects to determine what's missing.

SELECT object_name(object_id)
SELECT object_name(766832133)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-17 : 01:41:49

CheckDB does not work 'harder' in Emergency mode. Irreparable errors are irreparable errors no matter what the state of the DB.

Fortunately, these can be manually repaired.
http://sqlinthewild.co.za/index.php/2009/08/26/corruption-in-sysdependencies/

Original cause of this is likely someone directly updating the system tables on SQL 2000. That was done too often and could have nasty side effects (like these)

--
Gail Shaw
SQL Server MVP

DanielDucharme
Starting Member

11 Posts

Posted - 2010-08-17 : 13:56:12

Ok running the select statements suggested showed that the second line on all three databases was a stored procedure which I dropped and then recreated and that error is now gone. The other object is listed as NULL however so I am not sure how to fix that one. Here is the new DBCC CHECKDB output for all my databases:

Database: Coventry
Database: EastGreenwich
Msg 8992, Level 16, State 1, Line 17
Check Catalog Msg 3853, State 1: Attribute (object_id=766832133) of row (class=0,object_id=766832133,column_id=0,referenced_major_id=212208552,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=766832133) in sys.objects.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'EastGreenwich'.
Database: CentralFalls
Database: Warren
Database: NorthProvidence
Database: Meriden
Database: Charlestown
Msg 8992, Level 16, State 1, Line 17
Check Catalog Msg 3853, State 1: Attribute (object_id=766832133) of row (class=0,object_id=766832133,column_id=0,referenced_major_id=212208552,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=766832133) in sys.objects.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Charlestown'.
Database: Jamestown
Database: TrainingDB
Msg 8992, Level 16, State 1, Line 17
Check Catalog Msg 3853, State 1: Attribute (object_id=766832133) of row (class=0,object_id=766832133,column_id=0,referenced_major_id=212208552,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=766832133) in sys.objects.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'TrainingDB'.
Database: Upton

Now seeing as how it is NULL I don't know what I should be dropping to fix it.

Also GilaMonster you mention that there is no difference between running CHECKDB on a normal database and on in emergency mode but the MSDN says it performs 4 additional tasks in emergency mode that it doesn't do in normal mode. I was going to quote the exact section here for you to make sure I am not just misunderstanding something but the MSDN seems to be down right now. Also 4 errors that hadn't been fixed when I ran CHECKDB with REPAIR_ALLOW_DATA_LOSS without being in emergency mode were fixed when I put it into emergency mode. Now is the risk just too great that I should be manually repairing rather then trying the emergency mode repair or am I just misunderstanding something. Thanks for all your help so far.

Daniel Ducharme
Computer Systems Analyst

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-17 : 17:31:25

Maybe I phrased that statement badly. Irreparable errors (like these schema errors) are irreparable errors, no matter what status the DB is in. From what you wrote I thought it was these that you put the DB into Emergency to fix.
If you can't find that object in sys.objects, the only practical way you're going to fix this is to export data, script objects and create new database and load everything back up.

Pity you didn't post the errors before running the repairs. Would have liked to see and advise from the start.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources