SQL Database corruption in system objects 'sys.ifts_comp_fragment_ referenced' for FTS object fragmentation

  • Article

In this blog, I will share one of the unique corruption scenario i came across, where corruption was in system meta data keeping information FTS object fragementation.

Here the corruption was in system object 'sys.ifts_comp_fragment_1467152272_6792'. DBCC CHECKDB for the database was coming out with below error message.

ERROR

=======

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057596473638912 (type Unknown), page (1:2867044). Test (sorted[i].GetOffset () >= max) failed. Slot 0, offset 0x1 overlaps with the prior row.

CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.

Object ID 10391752, index ID 1, partition ID 72057596477833216, alloc unit ID 72057596479799296 (type In-row data): Page (1:2867043) could not be processed. See other errors for details.

Table error: Object ID 10391752, index ID 1, partition ID 72057596477833216, alloc unit ID 72057596479799296 (type In-row data), page (1:2867281). Test (sorted[i].GetOffset () >= max) failed. Slot 0, offset 0x1 overlaps with the prior row.

Table error: page (1:2867282) allocated to object ID 10391752, index ID 1, partition ID 72057596477833216, alloc unit ID 72057596479799296 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.

Table error: Object ID 10391752, index ID 1, partition ID 72057596477833216, alloc unit ID 72057596479799296 (type In-row data). Page (1:2867283) is missing a reference from previous page (1:2867282). Possible chain linkage problem.

CHECKDB found 0 allocation errors and 8 consistency errors in table 'sys.ifts_comp_fragment_1467152272_6792' (object ID 10391752).

CHECKDB found 0 allocation errors and 9 consistency errors in database 'DBName'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DBNAME, noindex).

ENVIRONMENT

=============

SQL = SQL 2008

Looking at the above error the corruption seems to be on system table, however this system object wasn’t visible in SQL user database, neither on master database.

Checked the error and could see corruption in system table 'sys.ifts_comp_fragment_1467152272_6792'

The problem database was approximately 23 GB in size.

There are 0 rows in 0 pages for object "TableName".

CHECKDB found 0 allocation errors and 9 consistency errors in database 'DBName'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DBName).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Checked the complete system and application log however no indication of Server crash or hardware issue.

DBCC CHECKDB on master was clean.

The problem object relates to Full text search and looks meta data for FTS fragments or document information.

Ran below query and this came out with below information. This query checks the fragmentation in objects and their size.

Select * from Sys.fulltext_index_fragments

table_id fragment_id fragment_object_id timestamp status data_size row_count

----------- ----------- ------------------ ------------------ ----------- -------------------- --------------------

1165247206 37755 1563165360 0x00000000001B9583 4 47043687 210215

1165247206 37813 183684448 0x00000000001B95EE 4 10254 175

1165247206 37815 199684505 0x00000000001B95F1 4 5201 89

1165247206 37835 567685816 0x00000000001B961C 4 53 1

1165247206 37836 583685873 0x00000000001B961F 4 5308 91

1467152272 6792 10391752 0x00000000001B7002 4 605258819 2133650

1467152272 7302 1547829272 0x00000000001B8632 4 2025872 23044

1467152272 7545 1701593846 0x00000000001B9253 4 1172022 15027

1467152272 7648 1243164220 0x00000000001B9561 4 577875 8024

1467152272 7676 535685702 0x00000000001B9619 4 167683

Here i got interested information the table and the fragment ID. If we check the table witnessed in the error its suffix is table + Fragment ID

So ran the query, select * from sysobjects where id = 1467152272 and got the object name, this object was then confirmed to be used in FTS catalog

So confirmed that this table "Sys.fulltext_index_fragments" keeps information on the FTS object / index level fragment information and this seems to be corrupt for some reason.

Performed Re-indexing on this user object did not help.

Ran DBCC CHECKTABLE ('Tablename') for this table and it came clean.

So the corruption is not on the user table but the system reference table.

Took the database backup and dropped the catalog using this table and re-created the catalog successfully.

Re-executed DBCC CHECKDB on the database and this time it came clean.

Comments

  • Anonymous
    January 07, 2015
    "Took the database backup and dropped the catalog using this table and re-created the catalog successfully. Re-executed DBCC CHECKDB on the database and this time it came clean." It's doesn't work for me.... after done as your suggestion, i searched nothing through  "Select * from Sys.fulltext_index_fragments " but the ifts_comp_fragment_data_space_***** still exist.....

  • Anonymous
    June 16, 2015
    Great job, thanks a lot!!!! Rebuilding the Full-Text catalog helped me resole the same corruption issue. Well done!