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

Article translations Article translations
Article ID: 2787112 - View products that this article applies to.
Expand all | Collapse all

On This Page

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.

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.

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.

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 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
ErrorMessage Text
3851An invalid row (%ls) was found in the system table sys.%ls%ls
3852Row (%ls) in sys.%ls%ls does not have a matching row (%ls) in sys.%ls%ls.
3853Attribute (%ls) of row (%ls) in sys.%ls%ls does not have a matching row (%ls) in sys.%ls%ls.
3854Attribute (%ls) of row (%ls) in sys.%ls%ls has a matching row (%ls) in sys.%ls%ls that is invalid.
3855Attribute (%ls) exists without a row (%ls) in sys.%ls%ls.
3856Attribute (%ls) exists but should not for row (%ls) in sys.%ls%ls.
3857The attribute (%ls) is required but is missing for row (%ls) in sys.%ls%ls.
3858The attribute (%ls) of row (%ls) in sys.%ls%ls has an invalid value.
3859Warning: 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

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 Event ID 17659 and event ID 3859 are logged when you update system tables in a SQL Server database


Properties

Article ID: 2787112 - Last Review: February 4, 2013 - Revision: 3.0
Applies to
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Standard
Keywords: 
kbsurveynew kbtshoot kbexpertiseadvanced KB2787112

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com