DBCC errors on 3.5TB database

DBDiva · February 22, 2022, 8:36pm

Hello, I have a SQL 2012 3.5 TB database with filestream, full text, file partitions, BLOB's & file tables.
I ran:
DBCC CHECKDB ([xxx]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY

error messages:
Msg 2576, Sev 16, State 1, Line 1 : The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (3:6134807) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617098539008 (type Unknown), but it was not detected in the scan. [SQLSTATE 42000]
Msg 2576, Sev 16, State 1, Line 1 : The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (3:57752675) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617097949184 (type Unknown), but it was not detected in the scan. [SQLSTATE 42000]
Msg 2576 , Sev 16, State 1, Line 1 : The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (3:67569457) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617098276864 (type Unknown), but it was not detected in the scan. [SQLSTATE 42000]
Msg 2576 , Sev 16, State 1, Line 1 : The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (3:70025250) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617098473472 (type Unknown), but it was not detected in the scan. [SQLSTATE 42000]

Msg 2575 , Sev 16, State 1, Line 1 : The Index Allocation Map (IAM) page ( 3:43978588) is pointed to by the next pointer of IAM page (0:0) in object ID 1138608757, index ID 1, partition ID 72057617007050752, alloc unit ID 72057617098735616 (type In-row data), but it was not detected in the scan. [SQLSTATE 42000]

Msg 8946 , Sev 16, State 1, Line 1 : Table error: Allocation page ( 3:43978588) has invalid IAM_PAGE page header values. Type is 1. Check type, alloc unit ID and page ID on the page. [SQLSTATE 42000]

Msg 7965 , Sev 16, State 1, Line 1 : Table error: Could not check object ID 1138608757 , index ID 1, partition ID 72057617007050752, alloc unit ID 72057617098735616 (type In-row data) due to invalid allocation (IAM) page(s). [SQLSTATE 42000]

Msg 8954, Sev 16, State 1, Line 1 : CHECKDB found 4 allocation errors and 0 consistency errors not associated with any single object. [SQLSTATE 01000]

Msg 8990 , Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 2 consistency errors in table 'sys.ifts_comp_fragment_426796928_238904' (object ID 1138608757 ). [SQLSTATE 01000]

Msg 8989, Sev 16, State 1, Line 1 : CHECKDB found 5 allocation errors and 2 consistency errors in database 'xxx'. [SQLSTATE 01000]

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (xxx). [SQLSTATE 01000]

I do not have a clean backup, as this was just ran for the first time. So, my guess it has probably been there for a few months.
I have not done anything yet to the database. The database is operational and not experiencing any issues.
I have done some googling.

Found that the Msg 2576 error is metadata/BLOB related, and they are in an orphaned state. MS resolution is to use the DBCC CHECKDB command together with the REPAIR_ALLOW_DATA_LOSS option to remove the orphaned IAM page.

It is the other Msg's that I am unfamiliar with:
SELECT i.name as 'InternalTableName',
'ParentTable1WithFullTextIndex' as ParentTable, -- parent table has full text
s.name as 'InternalTableSchema',
i.type_desc, internal_type_desc ,
i.parent_object_id,
o.name as 'Parent Object'
FROM sys.internal_tables i
inner join sys.objects o on i.parent_id = o.object_id
inner join sys.schemas s on i.schema_id = s.schema_id
where i.name = 'ifts_comp_fragment_426796928_238904

This points to a USER_TABLE = x_request_files
I ran dbcc checktable ('x_request_files') with no_infomsgs -- it was successful

How can I fix this?
How can I determine if there will be data loss if I run repair_allow_data_loss?
Thank u.

robert_volk · February 23, 2022, 3:59pm

If you have the space (on a separate storage device), see if you can make a full database backup first. And if you manage to repair it and/or otherwise fix the errors, make sure to get a full backup afterwards ASAP.

You can try a few things before DBCC repair_allow_data_loss:

Query sys.partitions and sys.allocation_units DMVs. JOIN on hobt_id=container_id, then also try partition_id=container_id (I think they are equal but try them both and compare results).
Use the above query to associate object_id to allocation_id, for all of the IDs listed in the DBCC output. sys.partitions will have object_ids, you can then JOIN to sys.objects to get the name.
For example, if you have an allocation_unit_id, query sys.allocation_units for that ID and join it to sys.partitions to get the object_id. Do the inverse if you have an object_id in the DBCC output.
Once you've gotten all those object/allocation links, save them someplace else, like another database or an Excel sheet. DEFINITELY NOT in the same DB, and as much as possible, on a different storage device than what the main database is on. (you want to avoid writing to that device in case there's defective hardware or drivers)
Once you know which objects are affected, you can try DBCC CHECKTABLE on each one and see if there's different output. It should be the same, but doesn't hurt the verify.
You can then try querying all the affected tables to see if their data can still be accessed. You can also use the bcp utility to export their data to a separate file (again, make sure it's on a different storage device). This is a secondary backup in case the repair operation loses data. If the tables are small, or you have enough space, you can also copy their data into a new, separate database.
Regarding how to determine if data was lost after repair, I can only suggest trying to copy the affected tables to another database, then comparing after the repair is done. You can also look at the CHECKSUM(), BINARY_CHECKSUM() functions to get values for each row, and CHECKSUM_AGG() to compare on the entire table. You need to generate these checksums before the repair, and save them elsewhere.

I've been able to correct DBCC CHECKDB errors using methods like these, without using the repair option and without losing data. Allocation errors don't always mean data is lost, it's just inconsistent pointers to the various underlying pages.

The ifts**** object is probably a full-text index, you can drop and recreate that as long as you can confirm that's what it is. That's why you need to the allocation/object mapping.

Sorry if this is verbose and/or not clear, I'm working on 3 hours of sleep at the moment. Also check the sqlskills.com site for Paul Randal's blog, he's written a lot about recovering from corruption, and wrote a lot of the DBCC CHECKDB code. If you're really stuck you can probably email him with the DBCC output.

DBDiva · February 23, 2022, 8:17pm

Thank you.
select * from sys.partitions, sys.allocation_units
where hobt_id=container_id
and allocation_unit_id in (72057617097949184, 72057617098539008, 72057617098276864, 72057617098473472) -- type Unknown
The first 4 msg (2575) does not return any rows

The Msg 2575, 7965 - returns the Internal table = ifts_comp_fragment_426796928_238904, which is full text. The parent table = x_request_files
select * from sys.partitions, sys.allocation_units
where hobt_id=container_id
and allocation_unit_id = 72057617098735616 -- object_id = 1138608757 ifts_comp_fragment_426796928_238904 (full text), partition_id = 72057617007050752

I cannot run dbcc checktable('ifts_comp_fragment_426796928_238904') - hence system table

There is 1 full text index on x_request_files.
FT Catalog Name = RequestFulepdfCatalog
UniqueIdxName = PK_x_request_files1
Column Name = OCRContent
Column Name = OriginalFileContent
How can I drop & recreate the index?? Is there any risk of that?? I thought I read it could be bad...

robert_volk · February 23, 2022, 9:20pm

I'd say DROP the fulltext index first:

Then run DBCC CHECKDB again. Don't create any new indexes/objects until you've cleared or repaired all your existing errors, and made a new full database backup.

You may also need to DROP fulltext catalogs:

I don't think there's any harm in dropping all the fulltext objects, as long as you have a means to re-create them after you've cleared all the CHECKDB errors.