SQL Server: Incorrect PFS free space information for page (1:xxxx) in object ID xxxxxx:
“Incorrect PFS free space information for page (1:233791) in
object ID 367392428, index ID 1, partition ID 72057594180730880, alloc unit ID 72057594222018560 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL. CHECKDB found 0 allocation errors and 1 consistency errors in table ‘xxxxxxx’ (object ID 367392428).
We have a maintenance plan which is scheduled to run on weekly basis and checks the database integrity for specified user databases in production instance.
I found above error in SQL Server log. Clearly from the error message we can say that it’s not the page corruption. There is a special page called PFS (Page Free Space) which indicates the percentage of page full. It actually helps free space scanner while inserting data.
In this case PFS is 100% means it indicates page is full whereas in real the page is empty. DBCC commands will fail due to this wrong calculation.
Resolution:
To resolve this I have tried the below procedure.
-
Take a full backup of the database (Based on the size if it’s a huge DB take the backup of table just by using “SELECT * INTO <bkp_table> FROM <corrupted_table>)
-
From sql log we can find the object (Table/Index) name and ID
-
Put database in single user mode
ALTER DATABASE <Corrupted_Table_DatabaseName>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
-
Run DBCC CHECKTABLE() on specific table / index
DBCC CHECKTABLE(<Corrupted_Table>) WITH ALL_ERRORMSGS;
-
It will reproduce the “PFS” error.
-
To fix this execute the below two statements one after other.
DBCC CHECKTABLE(<Corrupted_Table>,REPAIR_FAST) WITH ALL_ERRORMSGS;
GO
DBCC CHECKTABLE(<corrupted_table>,REPAIR_REBUILD) WITH ALL_ERRORMSGS;
-
For most of the cases the problems must be fixed with above two checks.
-
If not goahead and execute the below statement
DBCC CHECKTABLE(<Corrupted_Table>,REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
-
For first time it executes and completes with the same PFS error message.
-
Re execute the step number 8, now this time it fix the issue and avoid the PFS error.
-
Put database in multi user mode
ALTER DATABASE <Corrupted_Table_DatabaseName> SET MULTI_USER;
If it’s not got resolved, put the database in single user mode, backup the corrupted table (using SELECT * INTO <bkp_table>) . Backup the table creation script with all key relationships, drop the corrupted table, rename the backup table with the original table and recreate all keys and relationships.
If it still not gets resolved there is only option. Restore the database with the latest possible backup.
