question

moboy-9601 avatar image
0 Votes"
moboy-9601 asked Cathyji-msft edited

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

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @moboy-9601

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".






5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.