This article discusses how to resolve two of the Msg 8992 errors encountered when running DBCC CHECKDB on a database that has been upgraded from SQL 2000 to SQL 2005. The two messages that will be covered are:

1. Row in sys.columns does not have a matching row in sys.objects
2. Attribute (parent_object_id) in sys.objects does not have a matching row in sys.objects.

Both of these errors are because, in SQL 2000, one was allowed to directly modify system tables. Someone (surely not you!) deleted records directly from the sysobjects table instead of using the proper DROP statements. If the object of the deleted record had columns (i.e. tables), then there would be orphaned records in the syscolumns table, and you would see the first message above. If the object had other child objects, there would be orphaned records in the sysobjects table, and you would see the second message above.

These errors show up when running DBCC CHECKDB in SQL 2005, and not in SQL 2000, because in SQL 2005 the DBCC CHECKDB statement has been enhanced to run DBCC CHECKCATALOG, which picks up these errors.

The best method to fix these errors is before you migrate. In SQL 2000, run DBCC CHECKCATALOG and fix the errors there.

If you’ve already migrated to SQL 2005, and you cannot go back to SQL 2000 to fix the problems, then there are only two options available to fix the issue. The method preferred by most of the SQL corruption gurus is to create another database, and copy the contents of the corrupted database into the new database. Depending on the size of your database, this could take quite some time.

The second method is to update the system tables directly. This is the method discussed in further detail in this article. When directly modifying the system tables, you run the risk of making things worse than they are now – possibly even making the database unusable. Because of this, directly modifying the system tables is UNDOCUMENTEDandUNSUPPORTEDby Microsoft.

If you are still determined to directly modify the system tables, start off by first making a good backup of your database. Then, verify your backup – until you do so, you don’t know if it’s good or not. In case you manage to really mess up the database, you can restore from this backup and start over (or, preferably, proceed with copying the data to a new database).

Part 1: Properly start up and log into SQL SERVER.

To fix either of these errors, you need to be able to directly modify the system tables. In order to even see the system tables, you must be logged in with a Dedicated Administrator Connection (DAC). In order to update the tables, the server must also be in single-user mode.

1. If you are going to connect to the server using the DAC connection from a remote computer, ensure the server is set up to accept remote admin connections by following the instructions at http://msdn.microsoft.com/en-us/library/ms190468%28SQL.90%29.aspx.
2. Stop the SQL Server service:
   1. Launch SSMS.
   2. In the Registered Servers window, right-click on the server, and STOP the server. This will also require stopping SQL SERVER AGENT, if it is running.
   3. Close SSMS.
3. Start SQL Server in single-user mode (this step must be performed directly on the server, either physically or through a remote desktop connection):
   1. Open Command Prompt on the server.
   2. Navigate to the SQL Server binary directory (i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn)
   3. Start SQL in single-user mode: sqlservr –m (if this is a named instance, add –s<instance name>).
   4. Leave this Command Prompt window open until you are finished and ready to shut down the server.
4. Log in to SQL with a DAC connection:
   1. Launch SSMS in a disconnected state (when SSMS prompts for a login to connect to the server with, click on cancel).

   2. Open a new query window. When prompted for the server and login information, put “ADMIN:” before the server name, and use login credentials that have sysadmin access to the server. (Note that you can use SSMS 2008 to connect to a SQL 2005 server.)

      

5. Since you are in single-user mode, you can only open up this one connection to SQL Server. Everything you do must be done in this one query window.

Part 2: How to fix Msg 8992: Row in sys.columns does not have a matching row in sys.objects.

As previously mentioned, this error is because objects with columns have been deleted from sysobjects and have left orphaned column entries behind in syscolumns. Determine which objects need the column information deleted and delete that column information with the following steps:

1. In the Query window opened in Part 1 above, go to the corrupted database with the USE command.

2. 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)
            
   

3. Copy the results of the above command into the query window, and run the statements.

Part 3: How to fix Msg 8992: Attribute (parent_object_id) in sys.objects does not have a matching row in sys.objects.

As previously mentioned, this error is because the objects manually deleted have child objects. Determine which objects have non-existent parent objects and delete those objects with the following steps:

1. In the Query window opened in Part 1 above, go to the corrupted database with the USE command.

2. 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 (SELECTobject_id FROM sys.objects)
          AND parent_object_id > 0

3. Copy the results of the above command into the query window, and run the statements.

Part 4: Restoring SQL Server to normal.

After you have finished repairing your corrupt database, you need to return the server to normal operation:

1. Close the query window in SSMS.
2. Go to the Command Prompt window, and press CTRL+C. This will prompt you to ensure that you want to shut down SQL Server; respond with Y.
3. Restart the SQL Server:
   • In SSMS, open up the Registered Servers window.
   • Right-click on the server, and select START.

I wish that I could claim credit for all of the above, but the truth is that I obtained most of this information from an excellent blog post from Paul S. Randal: “TechEd Demo: Using the SQL 2005 Dedicated Admin Connection to fix Msg 8992: corrupt system tables” (http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Using-the-SQL-2005-Dedicated-Admin-Connection-to-fix-Msg-8992-corrupt-system-tables.aspx). Paul’s blog post describes how to fix the first corruption issue one record at a time, utilizing the SQLCMD utility. The discussion on this blog entry covers the second corruption issue. I’ve adapted both of these to utilize SSMS, and to detect and generate sql commands to repair all of the corruption at once. After all, if someone has been directly modifying the system tables, you’re not likely to have just one orphaned record… you’re likely to have thousands of them.

Other recommended articles:

1. Article by Gail Shaw on SQLServerCentral: Help, my database is corrupt. Now what? (http://www.sqlservercentral.com/articles/65804/)
2. Blog entry by Gail Shaw: Missing references in sys.sql_dependencies. (http://sqlinthewild.co.za/index.php/2009/08/26/corruption-in-sysdependencies/)