Fix corruption on internal system table sql server based on integrity check failure

mo boy 396 Reputation points
2022-05-17T22:04:47.35+00:00

Dear Experts,

The integrity job for on of our databases failed with below error:

Table Error :(Page 1:252) allocated to object 123131314, index ID 1, partition ID 732423423423, alloc unit ID 23423524525 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

On checking, it appears this is related to an internal table ifts_comp_fragment_23423423_543534534 and the internal type desc is showing as 'FULLTEXT_COMP_FRAGMENT). Is this something like a cause for concern. How do we go about fixing these type of errors on the internal tables?

Please provide your inputs. Thank you.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,488 questions
0 comments No comments
{count} votes

Accepted answer
  1. CathyJi-MSFT 21,066 Reputation points Microsoft Vendor
    2022-05-18T02:46:05.307+00:00

    Hi @mo boy

    The best solution if DBCC CHECKDB reports errors is to restore from the last known good backup. If you do not have the backup, you can use repair option of DBCC CHECKDB to fix this issue. Or use REPAIR_REBUILD option.

    1.Change the database to single user mode.

    ALTER DATABASE <database_name> SET SINGLE_USER  
    

    2.Repair the database using a safe repair that will not cause data loss (if possible).

        DBCC CHECKDB ('<database_name>', REPAIR_REBUILD)  
    

    3.Change the database to back to multi user mode.

     ALTER DATABASE <database_name> SET MULTI_USER  
    

    If it is still not work, use REPAIR_ALLOW_DATA_LOSS option, but this option will have some data loss. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

    DBCC CHECKDB ('<database_name>', REPAIR_ALLOW_DATA_LOSS)  
    

    In addition, since DBCC CHECKDB with any of the REPAIR options are completely logged and recoverable, Microsoft always recommends a user use CHECKDB with any REPAIR options within a transaction (execute BEGIN TRANSACTION before running the command) so that the user can confirm he/she wants to accept the results of the operation. Then the user can execute COMMIT TRANSACTION to commit all work done by the repair operation. If the user does not want to accept the results of the operation, he/she can execute a ROLLBACK TRANSACTION to undo the effects of the repair operations.

    Refer to MS document DBCC CHECKDB (Transact-SQL).


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

0 additional answers

Sort by: Most helpful