Select the product you need help with

"Msg 8992" error message and inconsistent metadata in the SQL Server system catalogs

Article ID: 2787112 - View products that this article applies to.

Symptoms

You experience one of the following issues in Microsoft SQL Server.

Issue 1

Assume that you create an inconsistency in the system metadata when you manually update the system tables in a SQL Server database. Then, you try to run a DBCC CHECKDB command in order to identify inconsistencies in the system metadata tables. In this situation, the following error message is returned:

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=<Object ID>,key_index_id=<Index ID>) of row (object_id=<Object ID>) in sys.foreign_keys does not have a matching row (object_id=<Object ID>,index_id=<Index ID>) in sys.indexes.

Issue 2

When you update the system tables in SQL server, you receive a warning message that resembles the following:

Warning: System table ID <Table ID> has been updated directly in database ID <Database ID> and cache coherence may not have been maintained. SQL Server should be restarted.

In this situation, if you run a DBCC CHECKDB or DBCC CHECKCATALOG command, the warning message together with the date and time that the system catalogs were last changed are returned. Additionally, the following warning message is also logged in the SQL Server log:

Warning: The system catalog was updated directly in database ID <Database ID>, most recently at <Log time>.

Note This information is stored in the database header and persists for the lifetime of the database.

Back to the top | Give Feedback

Cause

This issue occurs because SQL Server does not support manual updates to system tables. System tables should be updated only by the SQL Server database engine.

Note You can view the data in the system tables by using the system catalog views.

Back to the top | Give Feedback

Resolution

To resolve this issue, use one of the following methods.

Method 1

If you have a clean backup of the database, restore the database from the backup.

Note This method works only if the backup does not have inconsistencies in the metadata.

Method 2

If you cannot restore the database from a backup, export the data and the objects to a new database. Then, transfer the contents of the manually-updated database into the new database. Note You cannot repair inconsistencies in the system catalogs by using the REPAIR options in the DBCC CHECKDB commands. Therefore, because the command cannot repair metadata corruption, the command does not provide any recommended repair level.

Back to the top | Give Feedback

More information

For more information about the System Center Advisor rule that detects modifications that are made to system catalogs, click the following article number to view the article in the Microsoft Knowledge Base:

2688307

(http://support.microsoft.com/kb/2688307/ )

Event ID 17659 and event ID 3859 are logged when you update system tables in a SQL Server database

The following table lists the errors that indicate inconsistencies in the system catalogs. Usually, the following errors are generated together with an "8992" error code.

Collapse this tableExpand this table

Error	Message Text
3851	An invalid row (%ls) was found in the system table sys.%ls%ls
3852	Row (%ls) in sys.%ls%ls does not have a matching row (%ls) in sys.%ls%ls.
3853	Attribute (%ls) of row (%ls) in sys.%ls%ls does not have a matching row (%ls) in sys.%ls%ls.
3854	Attribute (%ls) of row (%ls) in sys.%ls%ls has a matching row (%ls) in sys.%ls%ls that is invalid.
3855	Attribute (%ls) exists without a row (%ls) in sys.%ls%ls.
3856	Attribute (%ls) exists but should not for row (%ls) in sys.%ls%ls.
3857	The attribute (%ls) is required but is missing for row (%ls) in sys.%ls%ls.
3858	The attribute (%ls) of row (%ls) in sys.%ls%ls has an invalid value.
3859	Warning: The system catalog was updated directly in database ID %d, most recently at %S_DATE.

The following are examples of error messages that you may receive when you experience these issues:

Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID <Object ID> (object '<Object ID>') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.

Msg 3853, Level 16, State 1, Line 2
Attribute (object_id=<Object ID>) of row (object_id=<Object ID>,index_id=<Index ID>) in sys.indexes does not have a matching row (object_id=<Object ID>) in sys.objects.

Msg 3855, Level 16, State 1, Line 2
Attribute (data_space_id=1) exists without a row (object_id=<Object ID>,index_id=<Index ID>) in sys.indexes.

Msg 3852, Level 16, State 1, Line 1
Row (object_id=<Object ID>,index_id=1) in sys.indexes (type=U ) does not have a matching row (class=0,objid=<Object ID>,indexid=<Index ID>,rowsetnum=1) in sys.sysrowsetrefs.

Msg 3852, Level 16, State 1, Line 1
Row (object_id=<Object ID>,index_id=1) in sys.indexes (type=U ) does not have a matching row (class=0,objid=<Object ID>,indexid=<Index ID>,rowsetnum=1) in sys.sysrowsetrefs.

Msg 3853, Level 16, State 1, Line 1
Attribute (default_object_id=<Object ID>) of row (object_id=<Object ID>,column_id=<Column ID>) in sys.columns does not have a matching row (object_id=<Object ID>) in sys.objects.

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=<Object ID>) of row (object_id=<Object ID>,column_id=<Column ID>) in sys.columns does not have a matching row (object_id=<Object ID>) in sys.objects.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Additionally, you may receive a "Msg 211" error message that resembles the following if you check the consistency of a database by running a CHECKDB command.

Msg 211, Level 23, State 230, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.

Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded

This error message also indicates that the system catalogs contain inconsistent metadata.

Note If you check the consistency of a database that was upgraded from SQL Server 2000 by running a CHECKDB command, the command may return an "8992" error code and report inconsistencies in the system catalogs. This behavior occurs because the DBCC CHECKDB command in SQL Server 2000 does not include the functionality of the DBCC CHECKCATALOG command. Therefore, you would not detect these issues in SQL Server 2000 unless you ran the DBCC CHECKCATALOG command.

For more information about how to use the DBCC CHECKDB command, go to the following Microsoft website:

General information about the DBCC CHECKDB command

(http://msdn.microsoft.com/en-us/library/ms176064(SQL.90).aspx)

Back to the top | Give Feedback

References

For more information about issues that can occur when a SQL Server system table is manually updated, click the article number to view the article in the Microsoft Knowledge Base:

2688307

(http://support.microsoft.com/kb/2688307/ )

Event ID 17659 and event ID 3859 are logged when you update system tables in a SQL Server database

Back to the top | Give Feedback