DBCC CHECKDB Errors 1

[TheSQL]

Hello,

I'm encountering an error using DBCC CHECKDB on a specific database. The error test is as follows:

Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1723361404,referenced_minor_id=1) of row (class=0,object_id=469120962,column_id=0,referenced_major_id=1723361404,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=1723361404,column_id=1) in sys.columns.
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=2123362829,referenced_minor_id=1) of row (class=0,object_id=469120962,column_id=0,referenced_major_id=2123362829,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=2123362829,column_id=1) in sys.columns.

The objects referenced are both User Defined Functions, so I'm not entirely sure how much the error should concern me. Any insights would be appreciated.

Thanks much.

 

[mrdenny]

Any error messages from DBCC CHECKDB should be of concern.

Further down it should tell you what repair level you need to use to correct the issue.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[TheSQL]

The full text of the error is as follows. I don't see any repair levels - am I missing something?

Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1723361404,referenced_minor_id=1) of row (class=0,object_id=469120962,column_id=0,referenced_major_id=1723361404,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=1723361404,column_id=1) in sys.columns.
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=2123362829,referenced_minor_id=1) of row (class=0,object_id=469120962,column_id=0,referenced_major_id=2123362829,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=2123362829,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 'ProductionDB'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

[mrdenny]

Did you get this from a maintenance plan or by running DBCC CHECKDB manually from Query Analyzer / SSMS? If it's from a maintenance plan, try running "DBCC CHECKDB" in the ProductionDB database from QA / SSMS.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[TheSQL]

Maintenance plan history. Run through Query Analyzer, I get the following:

DBCC results for 'ProductionDB'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
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=1723361404,referenced_minor_id=1) of row (class=0,object_id=469120962,column_id=0,referenced_major_id=1723361404,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=1723361404,column_id=1) in sys.columns.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=2123362829,referenced_minor_id=1) of row (class=0,object_id=469120962,column_id=0,referenced_major_id=2123362829,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=2123362829,column_id=1) in sys.columns.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 40192 rows in 230 pages for object "sys.sysrowsetcolumns".
...
...
(etc)
...
...
DBCC results for 'timCatSales1Wrk'.
There are 0 rows in 0 pages for object "timCatSales1Wrk".
CHECKDB found 0 allocation errors and 2 consistency errors in database 'ProductionDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Still no help!

 

[mrdenny]

That's strange. It usually gives you a repair level to fix the problem.

Try using the REPAIR_FAST flag. You will need to kick all the users out of the database and set the database to single user mode to fix the problem.

DBCC CHECKDB (ProductionDB, REPAIR_FAST)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[TheSQL]

Sorry, I should have specified, it's SQL 2005. Evidently for SQL 05:

REPAIR_FAST
Maintains syntax for backward compatibility only. No repair actions are performed

Is there an 05 equivalent? I was going to try dropping and recreating the functions in the interim....I'll let you know how that fares as well. Thanks for your help so far!

 

[mrdenny]

Try using the REPAIR_REBUILD flag.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[TheSQL]

Hey - the drop and recreate worked. Looks like something was just buggered up in the metadata. Thanks much for your help.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog