Error 8992 and CHECKCATALOG Failures: System Metadata Is Inconsistent

Fix it now

8992 is the catalogue half of DBCC CHECKDB reporting that the system metadata tables no longer agree with each other. Microsoft states plainly that DBCC CHECKDB cannot repair this error and that the database must be restored from a backup. Your routes out are a restore, or moving the objects into a fresh database.

DBCC CHECKCATALOG (N'MyDb') WITH NO_INFOMSGS;
DBCC CHECKDB (N'MyDb') WITH NO_INFOMSGS, ALL_ERRORMSGS;

1. Note every Msg 3852 and 3853 line. Each names the system view and the row that has no partner.
2. Run the full CHECKDB as well, so you know whether pages are damaged too. Catalogue errors with page errors point at hardware.
3. Restore from the newest backup that passes both checks after a test restore. That is the only route that keeps everything.
4. If no clean backup exists, create a new empty database, script the schema out, and move the data across object by object.

Do not run REPAIR_ALLOW_DATA_LOSS expecting it to help. Microsoft’s own topic for 8992 says the error cannot be repaired, and the DBCC CHECKCATALOG reference repeats it: inconsistencies cannot be repaired and the database must be restored from a backup.

If the restored database passes both checks you are done. If you have no clean backup, the next section explains why there is nothing for repair to rebuild from.

Why it happens

The system catalogue is the database’s description of itself: which objects exist, which columns belong to them, which allocation units back which partitions. You read it through views such as sys.objects, sys.columns, sys.indexes and sys.partitions, but underneath it is a set of ordinary tables that only the engine may write. DBCC CHECKCATALOG walks the relationships between those tables and reports every one that does not hold.

Two detail messages accompany 8992 and they are worth reading rather than skimming. 3852 says a row in one system view has no matching row in the view that should describe it. 3853 says an attribute of such a row points at a row that does not exist. Both are severity 10, which is why they arrive looking like information rather than an alarm, and both name the exact views and identifiers involved.

When CHECKDB finds a damaged data page it can often rebuild the structure around it, because the same information exists in more than one place. Catalogue rows do not have that redundancy. If the row describing a column is gone, nothing else in the database knows what that column was. That is why Microsoft’s own words on this error are that DBCC CHECKDB cannot repair it, and that if you cannot restore from a backup you should contact Microsoft Support. Running repair on an 8992 costs you an outage and a single-user window for nothing.

Full reference

Where catalogue damage usually comes from

The reason many people have never run this check

DBCC CHECKDB runs CHECKCATALOG as part of its work, with one exception that matters: specifying TABLOCK stops it. Microsoft documents that TABLOCK limits the checks performed, that DBCC CHECKCATALOG is not run on the database, and that Service Broker data is not validated. A nightly maintenance job carrying TABLOCK to make the check finish faster has therefore never checked the catalogue, and catalogue damage in that estate will surface as a failed restore or a failed upgrade rather than as a nightly alert.

How CHECKCATALOG behaves when you run it directly

• Syntax is DBCC CHECKCATALOG [ ( database_name | database_id | 0 ) ] [ WITH NO_INFOMSGS ]. Passing 0 means the current database.
• The database must be online.
• It uses an internal database snapshot for transactional consistency, exactly as CHECKDB does.
• If the snapshot cannot be created it acquires an exclusive database lock instead, so it is not always non-blocking.
• Against tempdb it performs no checks at all, because snapshots are not available there.
• It does not check FILESTREAM data, which lives on the file system rather than in the database.

Deciding between a restore and a rebuild

What not to try

Direct writes to system tables are unsupported and are the fastest way to turn a recoverable database into an unrecoverable one. Even where a mechanism exists to do it, using it puts the database outside support, and the row you invent will not carry the internal state the engine expects. If you get to the point of considering it, the honest options are a restore, a migration into a clean database, or Microsoft Support.

Equally, do not defer this indefinitely on the grounds that the application still works. Inconsistent metadata tends to surface later as a backup that fails, an upgrade that will not run, or a restore that stops half way. Plan the move into a clean database while the data is still readable and the choice is still yours.

Every code this article covers

Confirm the fix worked

1. Run DBCC CHECKCATALOG (N'MyDb') WITH NO_INFOMSGS; and confirm it returns nothing.
2. Run a full DBCC CHECKDB (N'MyDb') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm it is clean.
3. Take a full backup and confirm RESTORE VERIFYONLY passes against it.
4. If you migrated, compare object counts between the two databases: SELECT type_desc, COUNT(*) FROM sys.objects GROUP BY type_desc;
5. Confirm your scheduled CHECKDB job does not specify TABLOCK, or the catalogue check will keep being skipped.

Questions people ask about this