SQL Server, consistency error that I can’t fix

DBCC CHECKDB on a user database returns this error

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3851, State 1: An invalid row (class=128,depid=65536,depsubid=0) was found in the system table sys.syssingleobjrefs (class=128).

This error is in a user database, not in master! Internet is full of articles dedicated to this error when people try to check the master database, restored on a different server. That’s not the case.

I tried all usual magic without any success.

DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS
--error

ALTER DATABASE DBCopy SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB (DBCopy, REPAIR_ALLOW_DATA_LOSS) 
--output reports the same error

DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS
--error

--no hope but just in case
ALTER DATABASE DBCopy SET EMERGENCY

DBCC CHECKDB (DBCopy, REPAIR_ALLOW_DATA_LOSS) 
--the same error reported

DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS
--error

ALTER DATABASE DBCopy SET MULTI_USER

We don’t have a backup that is free from this error.
This error appears completely harmless. The DB and apps work completely fine.

Microsoft SQL Server 2019 (RTM-CU14)

The DB is in-memory enabled.

Anything to try before I start to re-build the DB?

P.S. I rebuild the database.

It appears that you have a row in sys.syssingleobjrefs that does not correspond to rows in other system tables.

sys.syssingleobjrefs is listed as a System Base Table, and is only accessible over a DAC (administrator) connection. You can access it using sqlcmd.exe -A.

The documentation says:

To bind to a system base table, a user must connect to the instance of SQL Server by using the dedicated administrator connection (DAC). Trying to execute a SELECT query from a system base table without connecting by using DAC raises an error.

Important

Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.

I would only ever advise to do so when there is literally no other option. Messing with system tables destroy your database.

So you connect while logged in as an Administrator, or by logging in as sa. You probably want to just delete the invalid row, hopefully that should fix the issue.

WARNING!:

Do this entirely at your own risk. I strongly suggest you take a backup of the database, restore it to an entirely different instance, and test this there first.

USE [DBCopy];
GO

ALTER DATABASE DBCopy SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DELETE FROM sys.syssingleobjrefs
WHERE class = 128
  AND depid = 65536
  AND depsubid = 0;

GO

DBCC CHECKDB (DBCopy, REPAIR_ALLOW_DATA_LOSS)
  WITH NO_INFOMSGS, TABLOCK;

GO

Repairing or deleting the corrupted lines will not work since this system table is not writable through classic T-SQL.

Depending on the size of that user database, it may be possible to dump/restore the database in order to rebuild the system catalog’s and thus remove the desync between objects and catalog.

I suggest you not to backup/restore but dump/restore, because a backup/restore will keep the desync between objects and catalog.

First, make sure the application above the database is shutdown, or at least that the connection pool to database is off.

You can first set your database in single user mode (which will imply a downtime for application if it’s not off).

ALTER DATABASE DBCopy SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Then through SSMS use right-click on the database concerned and select “Tasks > Generate scripts…”

Once in the following Wizard, select the objects to export (those referenced by your system table).

After the export is done, drop the objects referenced by your system table and restore them through the execution of the exported script.

Then get the database back into MULTI_USER :

ALTER DATABASE DBCopy SET MULTI_USER;

I suggest you, to try the procedure through a pre-production or benchmark environment first. This way, you will be able to test the solution without impacting the production at all.

PS : To find out which object is concerned by your system table, you will have to dig through the syssingleobjrefs system table (as @Charlieface said with sqlcmd -A which is the Administrator access through DAC).

If you don’t have sqlcmd installed on the server you may have to enable “Remote DAC” to use Administrator access through network.

PS (2) : The trickiest part will probably to find out which object is related to your corrupted data since these are system and undocumented tables, it is hard to tell you what object relies behind that reference.

PS (3) : If you can’t put the finger on which object is referenced by that system table. The last option would be to dump/restore all the objects of the table (which will of course involve a bigger downtime).