The second type of corruption I want to look at is that of missing references in sys.sql_dependencies. This typically appears on SQL 2005 in a database upgraded from SQL 2000.

On SQL 2000 it was possible to make direct updates to the system tables, and, in my opinion, if was done too often without the DBAs realising the long-term consequences.

As an interesting aside, early in 2005 I attended a series of pre-launch courses on SQL 2005. Among the other attendees was a senior DBA from one of our large banks. When he heard that 2005 hid the system tables and that it was no longer possible to update them, he went on a 10 minute rant about how MS was making his life impossible and removing essential features, etc, etc. It turned out that he did direct updates to drop users, drop logins, link logins to users, drop columns from tables and several other things. He absolutely would not accept that there were other ways to do those tasks and that modifying the system tables was risky. I’d hate to see the state of that database…

Back to the point…

There’s no referential integrity between the system tables, so it’s possible, in SQL 2000, to drop a row from sysobjects but have rows in syscolumns that reference that object. In SQL 2000, CheckDB did not run CheckCatalog and, as such, these errors often went unnoticed until the DB gets upgraded to SQL 2005 or higher

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

CheckDB will not repair these kinds of errors and there’s usually only two ways of fixing schema corruption

• Directly edit the system tables, which is decidedly non-trivial on versions later than 2000
• Script all the objects, export all the data and rebuild the database

For errors in sys.sql_dependencies (and only for errors in sys.sql_dependencies), there’s another solution. The error message lists two object_ids (in the case of the one above, that’ll be object_id=2132514976 and object_id=595793480). Query the system catalog to see what these objects are

SELECT name, type_desc FROM sys.objects

WHERE object_id IN (2132514976, 595793480)

One of them will be either a stored procedure, a function or a view. Script out that stored procedure/view/function (if neither object is a table, do this for both of them) along with all the security settings. Then drop the object and recreate it.

This should clear the entries out of sys.sql_dependencies and stop CheckDB from throwing errors. As always, run CheckDB again to be sure everything’s fixed.