0

I have a corrupted database with the following error

Msg 8952, Level 16, State 1, Line 1
Table error: table 'sys.syscolpars' (ID 41). Index row of index 'nc' (ID 2) does not match any row of data. Additional or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:96551:30) with values (id = 1059235274 and name = 'binary_message_body' and number = 0 and colid = 15) pointing to the row of data identified by (id = 1059235274 and number = 0 and colon = 15).
Msg 8952, Level 16, State 1, Line 1
Table error: table 'sys.syscolpars' (ID 41). Index row of index 'nc' (ID 2) does not match any row of data. Additional or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:96551:32) with values (id=1059235274 and name='conversation_handle' and number=0 and colid=5) pointing to data row identified by (id=1059235274 and number=0 and package = 5).

I've been advised to restore from a non-corrupted backup. The problem is that the most recent one dates several weeks back. There are some later full backups, but they already have that consistency error. Could I somehow get the differential backup between the stable backup and my latest version? I have tried to do it using Microsoft SQL Server Management Studio on a Windows Server 2019, but I think it contains the difference with to the last created backup (which already contains corrupted data) and I don't know how to find the difference between the stable backup and the data that I currently have.

Is there a way to delete specific backups so it considers the stable backup as the latest one?

I'm using this version:

Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)   Jan 12 2022 22:30:08   Copyright (C) 2019 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
Improve this question
3
  • Maybe try a full rebuild of your database by copying all tables over into a new one? Or restore the backup and try to reconcile the changes?
    – Charlieface
    Commented Mar 30, 2022 at 23:20
  • Thanks @Charlieface, but I'm not sure how to do that. How do I reconcile the changes?
    – charade
    Commented Apr 1, 2022 at 10:38
  • You would have to write MERGE statements for every table which compare by primary key and add, delete or update any rows needed. Probably better to start with straight copies of whatever you have. SSMS Import/Export Wizard might be a good place to start, there are others also. If any table fails then do a MERGE over a backup. Don't forget to copy all view, function, procedure and type definitions, as well as indexes, and any management data like certificates and users
    – Charlieface
    Commented Apr 1, 2022 at 10:55

1 Answer 1

Reset to default
2

Table error: table 'sys.syscolpars' (ID 41). Index row of index 'nc' (ID 2)

Is this the complete error message ?. The index ID is 2 is non-clustered index as per the error message you have posted. Try rebuilding the index it "may" fix the corruption, but since system tables are involved its most unlikely.

Is there a way to delete specific backups so it considers the stable backup as the latest one?

No that would not be possible. The restore always follow below sequence Full backup--Diff backup--Log backup. or Full backup--All log backups in sequence. If you have Last full backup which is not corrupted and then "ALL" log backups post that you would be able to get the database restored as near as possible to the corrupt one.

Other Options:

Could you try taking backup of current DB with continue_after_error backup database db_name to disk='Drive\folder\db.bak' with continue_after_error. Now try restoring it with continue_after_error and then run checkdb with repair_allow_data_loss to see how much data you loose.

Meanwhile also try to see how much data you loose with current stable backup, by restoring it. The amount of work needed is tedious but that is the way out here as I see.

Could I somehow get the differential backup between the stable backup and my latest version?

The diff backup will be linked to full backup after which it was taken so unless you restore that full backup, assuming it is stable and consistent, it would be of no use.

Improve this answer
2
  • Thanks for your answer! I have some doubts: - How can I rebuild the index? I haven't found any documentation about these tables and I don't understand what they do. They are 16 consistency errors in total, so it might be feasible to do. - How do I restore "All log backups"? Do they refer to the transactions being done in the database? - I've tried already checkdb with repair_allow_data_loss and repair_rebuild. It detects the 16 consistency errors, but then there is a crash of MSSQL CHECKDB with EXCEPTION_ACCESS_VIOLATION and process is terminated
    – charade
    Commented Mar 30, 2022 at 13:07
  • @charade sys.syscolpars is a system table, which you can only access via a DAC connection. It's unlikely that it will allow an index rebuild (these tables are normally read-only) but you could try. DBCC should not crash the server whatever the issue is, if it is doing so then report a bug.
    – Charlieface
    Commented Mar 30, 2022 at 23:19

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.