Database Corruption: Incorrect PFS Free Space

I've been lucky with database corruption during my career. I could probably count on one hand the number of times I've had to deal with it. A couple times, it was in a customer's environment--they managed it themselves, but called me in to help. The other incidents were ones I inherited from a backup I had to restore into a production environment. The first time it happened to me, I didn't realize it until days later when DBCC CHECKDB ran during a weekend maintenance window. After that, I added a new "rule" to my list: always run DBCC CHECKDB after restoring a database from someone else. That rule paid dividends today.

Here's the output I saw:

Msg 8914, Level 16, State 1, Line 50
Incorrect PFS free space information for page (1:2564368) in object ID 457768688, index ID 1, partition ID 72057619124060160, alloc unit ID 72057594116767744 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'tbl_Redacted' (object ID 457768688).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'db_redacted'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (db_redacted).

Although I had addressed this issue before, I had neglected to document it. This time around, my initial thinking was to rebuild the primary key index on the table:

ALTER INDEX PK_tbl_Redacted ON dbo.tbl_Redacted REBUILD;
GO
DBCC CHECKTABLE ('tbl_Redacted'); 
GO

But no luck. I tried the same thing a few more times, specifying different values for FILL FACTOR. But that didn't work either. Each time I ran DBCC CHECKTABLE, the same page number kept showing up as the culprit.

What I eventually ended up doing was copying all the rows from the source table into a #temp table, truncating the source table, then inserting all the rows from the #temp table back to the source table. My source table had a primary key, but no foreign keys referencing it. There was also an IDENTITY column, so I had to turn IDENTITY INSERT ON, and later turn it OFF. YMMV depending on the characteristics of your table.

USE db_redacted;
GO

BEGIN TRAN

SELECT *
INTO #tbl_Redacted
FROM dbo.tbl_Redacted;

TRUNCATE TABLE dbo.tbl_Redacted;

--[ColumnN] is an IDENTITY column. Turn on identity insert 
--so we can insert explicit values to that column.
SET IDENTITY_INSERT dbo.tbl_Redacted ON;

INSERT INTO dbo.tbl_Redacted
       (Column1, Column2, ... ColumnN)
SELECT Column1, Column2, ... ColumnN
FROM #tbl_Redacted
ORDER BY ColumnN;

--Turn identity insert off so future inserts
--will auto-generate values for [ColumnN]
SET IDENTITY_INSERT dbo.tbl_Redacted OFF;

/*
       COMMIT
       ROLLBACK
*/

--Assuming the above was committed...
DBCC CHECKTABLE ('tbl_Redacted');
GO

I would go on to realize this is an issue Paul Randal has known about for years. If I'm understanding correctly, running DBCC CHECKDB with a repair level of REPAIR_ALLOW_DATA_LOSS is actually OK in this one scenario (you shouldn't lose any data).

In my situation, I had a small-ish table with only about 13k records. The script I ran above was PDQ. For larger tables, moving all the rows from one table to another may not be practical. I'd be curious to know how much longer CHECKDB with REPAIR_ALLOW_DATA_LOSS would take. I can see some tradeoffs with both approaches.