One resolution to “sys.sql_dependencies does not have a matching row”

This particular issue I’m about to describe should be pretty rare, but I’m posting this here in the event that someone else encounters it and then finds this post and then hopefully applies the “minimum required dose” required to fix the problem instead of using more sweeping methods such as restoring from database backup… 

I had a case today where the following message (with different ids) was appearing in DBCC CHECKDB output across several SQL Server 2005 databases that had original been hosted on SQL Server 2000 instances:

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

Given that sys.sql_dependencies is on the deprecation path (in favor of sys.sql_expression_dependencies) – one might decide to ignore the errors, but living with this kind of noise in DBCC CHECKDB might cause you to then not pay attention in the event that more critical corruption errors occur.

While there are a few invasive solutions for clearing out a metadata mismatch between sys.sql_dependencies and sys.columns, if you’re encountering this issue between sys.sql_dependencies and sys.columns, first try EXEC sys.sp_refreshsqlmodule for the applicable object (for example – stored procedure) instead.  This system stored procedure will then update the metadata associated with the module and, as in today’s case, clean up the mismatches and corruption warnings in DBCC CHECKDB.

2 thoughts on “One resolution to “sys.sql_dependencies does not have a matching row”

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.