2

The issue was noticed when we ran dbbc checkdb.

EDIT: I looked up altering system tables and was successful in deleting the internal table from sys.sysschobjs$ from its id. https://www.sqlskills.com/blogs/paul/using-the-dedicated-admin-connection-to-fix-msg-8992-corrupt-system-tables/

now only one issue remains

Msg 211, Level 23, State 245, Line 1 Corruption in database ID 140707423584273, object ID 60 possibly due to schema or catalog inconsistency. Run DBCC CHECKCATALOG.

PRE FIX : It threw corruption error so we had moved on to do a dbcc checkcatalog of the database which resulted in

Msg 3852, Level 16, State 1, Line 1 Row (object_id=160237313) in sys.objects (type=IT) does not have a matching row (object_id=160237313,index_id=0) in sys.indexes.

Msg 3852, Level 16, State 1, Line 1 Row (object_id=160237313) in sys.objects (type=IT) does not have a matching row (object_id=160237313,column_id=1) in sys.columns.

Msg 3857, Level 16, State 1, Line 1 The attribute (internal_type=NULL,parent_id=NULL,parent_minor_id=NULL) is required but is missing for row (object_id=160237313) in sys.internal_tables.

I tried multiple things like repair-rebuild and restore with dataloss but nothing fixed this

The issue is due to

select * from sys.internal_tables where parent_id is null

which results in

name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published internal_type internal_type_desc parent_id parent_minor_id lob_data_space_id filestream_data_space_id
queue_messages_144237256 160237313 NULL 4 144237256 IT INTERNAL_TABLE 2023-02-25 16:07:28.660 2023-02-25 16:07:28.660 0 0 0 NULL NULL NULL NULL 0 NULL

Is there any way to recover this database ?

Any way to alter the system tables which this seems to be queue messages from service broker

dbcc checkdb repair_allow_data_loss
dbcc checkdb repair_rebuild

DBCC CHECKDB (DATABASE) WITH NO_INFOMSGS

Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3852, State 1: Row (object_id=160237313) in sys.objects (type=IT) does not have a matching row (object_id=160237313,index_id=0) in sys.indexes.

Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3852, State 1: Row (object_id=160237313) in sys.objects (type=IT) does not have a matching row (object_id=160237313,column_id=1) in sys.columns.

Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3857, State 1: The attribute (internal_type=NULL,parent_id=NULL,parent_minor_id=NULL) is required but is missing for row (object_id=160237313) in sys.internal_tables. CHECKDB found 0 allocation errors and 3 consistency errors not associated with any single object. CHECKDB found 0 allocation errors and 3 consistency errors in database 'kings'.

Msg 211, Level 23, State 245, Line 1 Corruption in database ID 140707423584273, object ID 60 possibly due to schema or catalog inconsistency. Run DBCC CHECKCATALOG.

Msg 211, Level 23, State 50, Line 1 Corruption in database ID 17, object ID 74 possibly due to schema or catalog inconsistency. Run DBCC CHECKCATALOG.

Completion time: 2023-04-26T11:10:20.5489320+05:45

Improve this question
5
  • Looks like a queue table that maybe should have been deleted anyway. What do you get if you do select object_id, name from sys.service_queues
    – Charlieface
    Commented Apr 25, 2023 at 16:12
  • @Charlieface it doesn't exist in sys.service_queues , I also assumed as much this is a queue entry that went wrong. The problem is mssql doesn't allow me to drop this object from its internal tables.
    – Rajin Tamrakar
    Commented Apr 26, 2023 at 5:14
  • Try DROP TABLE YourDb.sys.[queue_messages_144237256] from a DAC connection learn.microsoft.com/en-us/sql/database-engine/configure-windows/…
    – Charlieface
    Commented Apr 26, 2023 at 8:27
  • @Charlieface the table didn't exist while using DAC but It was listed in internal tables so i deleted it from internal tables now only one error remains i.e. Msg 211. I am currently trying to recover data from a tool.
    – Rajin Tamrakar
    Commented Apr 27, 2023 at 4:25
  • OK now you need to find which object is erroring and recreate it select * from sys.objects where object_id = 60 try drop and recreate it
    – Charlieface
    Commented Apr 27, 2023 at 11:32

2 Answers 2

Reset to default
-1

Known option is DBCC CheckDb (Databasename,REPAIR_REBUILD) for such errors in system tables. If it doesn't work or if it still shows such error, best and safest option would be to create a new database with same schema and migrate the data using Export Data option in SSMS or by using some third party application for data migration.

EDIT : I found a blog related to the issue. It suggests running sqlcmd in single user mode and deleting the orphaned queue data. https://www.sqlskills.com/blogs/paul/using-the-dedicated-admin-connection-to-fix-msg-8992-corrupt-system-tables/

PS : This is a undocumented method so use this at your own risk with caution and is not recommended in production databases.

Improve this answer
0
-3

Is there any way to recover this database ?

Sure. Just restore a backup. Also your storage solution probably caused the corruption, so you may need to restore it on another server.

Improve this answer
1
  • Database restore is impossible as the oldest backup available is of two months old. I checked and the issue exists for that database.
    – Rajin Tamrakar
    Commented Apr 25, 2023 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.