Investigate Root Cause of DBCC CHECKDB error | (SQLSTATE 42000)(Error 8992 | Catalog Msg 3853)

Question

---

• Details:
  • I am running Microsoft SQL Server 2022
  • I am running DBCC CHECKDB on a user database (of which raised this error), each saturday
  • This has happened on one on-prem Windows Server with installation of SQL server, with a predefined set of configs
  • Then after a month, I set up an additional on-prem Windows Server with same installation of SQL server, with the same predefined set of configs. The DBCC CHECKDB runs fine the first two weeks, then fails.
  • The error output after DBCC CHECKDB run on the user database: [SQLSTATE 01000] (Message 50000)  Command: DBCC CHECKDB ([<DATABASENAME_REDACTED>]) WITH ALL_ERRORMSGS 
    [SQLSTATE 01000] (Message 50000)  Check Catalog Msg 3853, State 1: Attribute (objid=317307915,indexid=1) of row (class=0,objid=317307915,indexid=1,rowsetnum=1) in sys.sysrowsetrefs does not have a matching row (object_id=317307915,index_id=1) in sys.indexes.  ...
    .. CHECKDB found 0 allocation errors and 12 consistency errors not associated with any single object And there is 11 more objects with the same error, I just didn't print them all here right now.
  • Both servers have several of these errors, with same message, but just different object_ids.
  • Both servers use the same type of hardware, server model, hardware provider, disk provider and disk model.
  • Both servers have restored to this userdatabase, from a database in an Azure SQL Managed Instance.
  • A small overview of errors returned by DBCC CHECKDB:

Date	Errors	Notes
See *Notes	12 errors	From 08/01/2025 to today(07/05/2025)
08/01/2025	12 errors	We tried REPAIR_ALLOW_DATA_LOSS here
04/01/2025	12 errors
28/12/2024	12 errors
21/12/2024	12 errors
14/12/2024	12 errors	Same amount of errors
07/12/2024	7 errors
30/11/2024	7 errors
23/11/2024	7 errors
16/11/2024	7 errors
09/11/2024	7 errors
02/11/2024	7 errors
26/10/2024	3 errors	First appearance
19/10/2024	No errors
17/10/2024	No errors
16/10/2024	No errors
12/10/2024	No errors
05/10/2024	No errors

---

• Troubleshooting steps already taken:
  • Firstly worth noting - No clean backups available.
  • Second thing worth noting - We have not experienced this causing or impacting the performance or functionality or integrity of the "user-created" objects (tables, views, constraints, procedures, indexes, )
  • DBCC CHECKDB on userdatabase have been runned on the Azure SQL Managed Instance as well, no DBCC CHECKDB Errors there
  • Performed several selects against:
    • sys.sysrowsetrefs,
    • sys.sysrowsets,
    • sys.partitions,
    • sys.objects,
    • sys.sql_modules,
    • sys.stats,
    • sys.indexes,
    • sys.allocation_units
    • sys.system_internals_partition_columns,
    To try and retrieve info, by Object_id (and/or rowsetid based on object_id from sys.sysrowsetrefs) None of the querries I ran, returned rows based on objid/rowsetid, except for sys.sysrowsetrefs ; the table that DBCC found does not have a matching row in sys.indexes for instance. It's like that this is a ghost object, just dangling reference to an object that is deleted or none-existent. And I can't retrieve any info about what this object was, why and how it is now gone, nor where on disk/page/partition it was stored.
  • I therefore tried running sp_clean_db_free_space manually in hope that it would eliminate the ghost records the DBCC CHECKDB was reporting - But nothing happened to the records.
  • Tried DBCC CHECKDB ([<DATABASENAME_REDACTED>], REPAIR) No errors repaired, still getting the same errors
  • Tried DBCC CHECKDB ([<DATABASENAME_REDACTED>], REPAIR_ALLOW_DATA_LOSS) No errors repaired, still getting the same errors. (After running this, I restored to full backup taken just before I ran this command, in order to rule out that REPAIR_ALLOW_DATA_LOSS could have messed this up even more.)
  • Tried running hardware providers disk check, and hardware check, to rule out if this could be I/O issue. No events registred, nor no errors or fails reported.

---

I really feel like I've tried everything here, but I'm eager to find the root cause to this, since It's happened on two servers now, not just one. Also a solution to how I should fix this going forward now would be highly appriciated!

Please feel free to ask me for more information or details

Answer 1

Check out this accepted answer for my question on DBA stack exchange:
https://dba.stackexchange.com/questions/346438/microsoft-sql-server-investigate-root-cause-of-dbcc-checkdb-error-sqlstate