SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Consistency Error Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DanielDucharme
Starting Member

USA
11 Posts

Posted - 08/16/2010 :  15:45:11  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 08/16/2010 :  21:38:32  Show Profile  Visit russell's Homepage  Reply with Quote
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)
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/17/2010 :  01:41:49  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

DanielDucharme
Starting Member

USA
11 Posts

Posted - 08/17/2010 :  13:56:12  Show Profile  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/17/2010 :  17:31:25  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Edited by - GilaMonster on 08/17/2010 17:33:17
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000