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
9
  • What is restore with data loss,?, did you tried restoring from known good backup ? Please post complete output of the checkdb
    – Shanky
    Apr 25 at 9:52
  • @Shanky Known backups are later than 2 months . repair allow data loss I meant this... \n ALTER DATABASE DbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE \n DBCC CheckDB ('DbName', REPAIR_ALLOW_DATA_LOSS) \n ALTER DATABASE DbName SET MULTI_USER
    – Rajin Tamrakar
    Apr 25 at 10:12
  • So when you ran checkdb with repair allow data loss it did not worked, what error came ? Also please post complete output of dbcc checkdb, add the output in question.
    – Shanky
    Apr 25 at 10:17
  • it told me to do dbcc checkcatalog which resulted in the above message.
    – Rajin Tamrakar
    Apr 25 at 10:18
  • 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
    Apr 25 at 16:12

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
2
  • 1
    this seems like a taxing job. I will give it a try but might be open to more suggestions.
    – Rajin Tamrakar
    Apr 25 at 12:14
  • I have also stumbled on to this blog through alternate means . Some issues have been fixed . Thanks for this though. Will shortly update question.
    – Rajin Tamrakar
    Apr 26 at 8:58
-2

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
3
  • 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
    Apr 25 at 12:14
  • Did you restore that backup to a different server that you have confidence in?
    – David Browne - Microsoft
    Apr 25 at 13:26
  • Yes I did but corruption of catalogs still occurs
    – Rajin Tamrakar
    Apr 26 at 5:12

Your Answer

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

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