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.
Hi…….I have a SQL Server 2000 (SP4 Standard Edition) system table sysindexes which has corrupted. Due to this i am not able to Reindex/Shrink Database…
Error:
Server: Msg 7105, Level 22, State 6, Line 1
Page (1:713), slot 3 for text, ntext, or image node does not exist.
Connection Broken
Please either post a question on one of the major SQL forums (MSDN, SQLServerCentral, SQLTeam) or call Microsoft’s customer support people.
This is way too complex and serious a problem to answer in the comments of a blog post.
Hi,
If we execute the following:
“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”
How can we assure that the dependencies from other objects wont face the same issue.
Thanks
By running CheckDB. It will pick up all these dependency errors and you can fix them in one go.
Hi Gail, Thank you for your reply. Okay, we will give this a try. We currently have 4 production databases which are having this error when we run DBCC CHECKDB.
HI Gail, Below is the error we encountered.
Msg 8992, Sev 16, State 1, Line 1 : Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1295447789,referenced_minor_id=1) of row (class=0,object_id=1400600278,column_id=0,referenced_major_id=1295447789,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=1295447789,column_id=1) in sys.columns. [SQLSTATE 42000]
Msg 8992, Sev 16, State 1, Line 1 : Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1295447789,referenced_minor_id=1) of row (class=0,object_id=1000650908,column_id=0,referenced_major_id=1295447789,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=1295447789,column_id=1) in sys.columns. [SQLSTATE 42000]
In our case, should we drop and recreate “referenced_major_id=1295447789”, “object_id=1400600278” and “object_id=1000650908”?
Thank you.
I Gail, Below is the error we encountered.
Msg 8992, Sev 16, State 1, Line 1 : Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1295447789,referenced_minor_id=1) of row (class=0,object_id=1400600278,column_id=0,referenced_major_id=1295447789,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=1295447789,column_id=1) in sys.columns. [SQLSTATE 42000]
Msg 8992, Sev 16, State 1, Line 1 : Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1295447789,referenced_minor_id=1) of row (class=0,object_id=1000650908,column_id=0,referenced_major_id=1295447789,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=1295447789,column_id=1) in sys.columns. [SQLSTATE 42000]
In our case, should we drop and recreate “referenced_major_id=1295447789″, “object_id=1400600278″ and “object_id=1000650908″?
Thank you.
PS: sorry I inputted the wrong email address on my first message
HI Gail,
Is there a way to simulate this error on a test database?
Thank you.
Best Regards,
Ricky
Hi Gail,
Do you know what could be the root cause of this “sys.dependencies” issue?
Thank you.
Best Regards,
Ricky
Err, yes I do know the root cause, from the blog post:
“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”
As for which you should drop, from the blog post:
“The error message lists two object_ids. Query the system catalog to see what these objects are. 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.”
Hence you should drop and recreate whichever is a function, procedure or view.
Hi Gail,
Thank for your reply. We encountered this issue only last week. We are using SQL 2005. CHECKDB ran successfully on the 28 Feb 2015. First time we saw it fail was last Saturday 07 March 2015.
Best Regards,
Ricky