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

Question

---

• Details:

  • I am running Microsoft SQL Server 2022

  Microsoft SQL Server 2022 (RTM-GDR) (KB5046861) - 16.0.1135.2 (X64) Oct 18 2024 15:31:58 Copyright (C) 2022 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: )

  • 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
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!

Answer 1

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.

Both servers have restored to this userdatabase, from a database in an Azure SQL Managed Instance.

The issue is due to the use of Managed Instance (MI) which has different uses of underlying system table metadata. If you're continuing to use MI link and Box together, you'll want to create a ticket with Microsoft. If you're not going to continue to use MI link with Box, then you can attempt to clean up the metadata yourself using DAC + single user mode utilizing the DBCC CheckDB output for the values to remove.

doesn't this put the DB into an unsupported state...? From docs: "Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario."

Yes, you're correct, that's why I stated you can attempt it yourself if you like. Otherwise, the guidance would be to raise a ticket with Microsoft. Once it's fixed on the MI side, the Box side will work appropriately (after the Box side uses a backup from the fixed MI database).

Answer 2

I just found out that this is a known issue, documented . I'm still going to raise a ticket with MS regardingly, becuase the Workarround will not work for me. The workarround will not work for my case because as mentioned, I can't find info about the object specified, so I cannot recreate nor drop it.