dbcc checkdb reports errors after upgrading from sql 2000 to 2005

Question

Post reply

dbcc checkdb reports errors after upgrading from sql 2000 to 2005

Gino-1124355

SSC Enthusiast

Points: 161
More actions
July 5, 2009 at 1:56 pm

#131772

I'm getting the following error after upgrading sql 2000 to 2005. I tried running DBCC CHECKDB (db, REPAIR_REBUILD)as well as restoring my DB with no success. I didn't get the errors prior to the upgrade:
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=913593422,referenced_minor_id=1) of row (class=0,object_id=1713596272,column_id=0,referenced_major_id=913593422,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=913593422,column_id=1) in sys.columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=913593422,referenced_minor_id=1) of row (class=0,object_id=1617595930,column_id=0,referenced_major_id=913593422,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=913593422,column_id=1) in sys.columns.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Any help is greatly appreciated.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 1

Can you stop the SQL Server and after sometime try to execute the same to see the outcome?

Thanks.

Gino-1124355 SSC Enthusiast Points: 161 More actions · Answer 2

I have tried reapplying sp2 and rebooted the server. Should I also stop sql?

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Run the below query::

DBCC CHECKDB('db') WITH No_INFOMSGS, ALL_ERRORMSGS[/b]

And post the output!

Regards

Sourav

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Dude,

Try this::

ALTER DATABASE [sourav] SET single_USER WITH NO_WAIT

DBCC CHECKDB ('sourav',REPAIR_ALLOW_DATA_LOSS)

This might help you to repair your DB after allowing some data loss.

Regards

Sourav

Thanks.

John.Sansom SSCertifiable Points: 7335 More actions · Answer 5

Sourav Mukherjee (7/5/2009)
Dude,
Try this::
ALTER DATABASE [sourav] SET single_USER WITH NO_WAIT
DBCC CHECKDB ('sourav',REPAIR_ALLOW_DATA_LOSS)
This might help you to repair your DB after allowing some data loss.
Regards
Sourav

To the original poster, please DO NOT carry out the action suggested above. Doing so "could" result in data loss to your database.

I recomend you continue with your investigations into identifying the exact cause of your issue, so that you may then implement the appropriate course of action.

John Sansom (@sqlBrit) | www.johnsansom.com

Gino-1124355 SSC Enthusiast Points: 161 More actions · Answer 6

Here are the results:

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'color=#red'.

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=913593422,referenced_minor_id=1) of row (class=0,object_id=1713596272,column_id=0,referenced_major_id=913593422,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=913593422,column_id=1) in sys.columns.

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=913593422,referenced_minor_id=1) of row (class=0,object_id=1617595930,column_id=0,referenced_major_id=913593422,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=913593422,column_id=1) 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 'CSL_DB'.

Thanks!

John.Sansom SSCertifiable Points: 7335 More actions · Answer 7

Reading through the following thread containing similar issues may prove benificial.

http://www.sqlservercentral.com/Forums/Topic724748-146-1.aspx

John Sansom (@sqlBrit) | www.johnsansom.com

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'color=#red'.

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=913593422,referenced_minor_id=1) of row (class=0,object_id=1713596272,column_id=0,referenced_major_id=913593422,referenced_minor_id=

1) in sys.sql_dependencies does not have a matching row (object_id=913593422,column_id=1) in sys.columns. Msg 8992, Level 16, State 1, Line 1

[Sourav] Seems there is a procedure in the DB, which references a column that doesn’t exist in the database. Run Sp_Depends ‘’\ select * from sys.sql_Dependencies to look for the case.

Can drop the procedure. Run the script again. Provide all the permissions. Again run “checkdb [db]” to look for the outcome.

Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=913593422,referenced_minor_id=1) of row (class=0,object_id=1617595930,column_id=0,referenced_major_id=913593422,referenced_minor_id=

in sys.sql_dependencies does not have a matching row (object_id=913593422,column_id=1) 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 'CSL_DB'.

[sourav]:

Per the error message provided by you, 2 consistency error is found in the Database.

It seems you had row(s) in any of the table, and there is an index for the table where this row wasn't represented. I.e., a missing row in an index. If you run a query which was driven by this index, then the query could potentially miss to return the row in question.

Regards

Sourav

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 9

Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=913593422,referenced_minor_id=1) of row (class=0,object_id=1617595930,column_id=0,referenced_major_id=913593422,referenced

Execute the below 2 queries to look for the objects involved in this regard:

SELECT Object_Name(913593422)

SELECT Object_Name(1617595930)

Regards

Sourav

Thanks.

Gino-1124355 SSC Enthusiast Points: 161 More actions · Answer 10

I think I've got it...I've recreated the SP's and so far each DB that I've tested is successful...thanks so much to y'all for your help and quick response.

Cheers,

John.Sansom SSCertifiable Points: 7335 More actions · Answer 11

Excellent news! Glad we could help.

John Sansom (@sqlBrit) | www.johnsansom.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Gino (7/5/2009)
I didn't get the errors prior to the upgrade:

The reason you didn't get errors before the update is that on SQL 2000, checkDB does not run checkcatalog and very few people run checkcatalog manually. Hence these errors can hide for years. The most common cause for these is someone doing direct updates to the system tables

On 2005 and higher, CheckDB does run checkCatalog and hence the errors.

No level of repair will fix these. CheckDB will never repair errors to the system tables. In this case (sysdependencies), the offending procs/views/functions can be simply dropped and recreated.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John.Sansom SSCertifiable Points: 7335 More actions · Answer 13

Thanks for stepping in with clarification Gail.

I for one certainly appreciate it.

John Sansom (@sqlBrit) | www.johnsansom.com

Gino-1124355 SSC Enthusiast Points: 161 More actions · Answer 14

Gino-1124355

SSC Enthusiast

Points: 161

July 6, 2009 at 3:24 pm

#1020710

Thanks John and Gail! Much appreciated!

Cheers,