Have you ever encountered the dreaded Msg 8992 error when running DBCC CHECKDB on a SQL Server database? This error typically occurs when you have upgraded a database from SQL Server 2000 to SQL Server 2005 and there are orphaned records in the system tables. In this article, we will discuss how to resolve two specific instances of this error and provide step-by-step instructions on how to fix them.

Msg 8992: Row in sys.columns does not have a matching row in sys.objects

This error occurs when objects with columns have been deleted from the sysobjects table, leaving behind orphaned column entries in the syscolumns table. To fix this error, follow these steps:

1. Connect to the SQL Server instance using a Dedicated Administrator Connection (DAC) and ensure that the server is in single-user mode.
2. Open a new query window and navigate to the corrupted database using the USE command.
3. Run the following SQL command:

SELECT DISTINCT 'DELETE FROM sys.syscolpars WHERE ID=' + convert(varchar(15),object_id)
FROM sys.columns  
WHERE object_id NOT IN (SELECT object_id FROM sys.objects)

Copy the results of the above command into the query window and execute the statements. This will delete the orphaned column entries from the syscolumns table.

Msg 8992: Attribute (parent_object_id) in sys.objects does not have a matching row in sys.objects

This error occurs when objects with child objects have been manually deleted from the sysobjects table. To fix this error, follow these steps:

1. Connect to the SQL Server instance using a DAC and ensure that the server is in single-user mode.
2. Open a new query window and navigate to the corrupted database using the USE command.
3. Run the following SQL command:

SELECT DISTINCT 'DELETE FROM sys.sysschobjs WHERE PID=' + convert(varchar(15), parent_object_id) + ' AND ID=' + convert(varchar(15),object_id)
FROM sys.objects
WHERE parent_object_id NOT IN (SELECT object_id FROM sys.objects) AND parent_object_id > 0

Copy the results of the above command into the query window and execute the statements. This will delete the orphaned child objects from the sysobjects table.

It is important to note that directly modifying the system tables is undocumented and unsupported by Microsoft. Therefore, it is recommended to fix these errors before migrating to a new version of SQL Server. However, if you have already migrated and cannot go back to the previous version, these steps can help you resolve the issue.

Remember to always make a backup of your database before making any changes to the system tables. This will allow you to restore the database in case anything goes wrong.

We hope this article has provided you with a clear understanding of how to resolve the Msg 8992 errors in SQL Server. If you have any questions or need further assistance, feel free to leave a comment below.