SQL Server: Incorrect PFS free space information for page (1:xxxx) in object ID xxxxxx:

 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.

Resolution:

1. 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>)
2. From sql log we can find the object (Table/Index) name and ID
3. Put database in single user mode

ALTER DATABASE <Corrupted_Table_DatabaseName>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

4. Run DBCC CHECKTABLE() on specific table / index

DBCC CHECKTABLE(<Corrupted_Table>) WITH ALL_ERRORMSGS;

5. It will reproduce the “PFS” error.
6. 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;

7. For most of the cases the problems must be fixed with above two checks.
8. If not goahead and execute the below statement

DBCC CHECKTABLE(<Corrupted_Table>,REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

9. For first time it executes and completes with the same PFS error message.
10. Re execute the step number 8, now this time it fix the issue and avoid the PFS error.
11. Put database in multi user mode

ALTER DATABASE <Corrupted_Table_DatabaseName> SET MULTI_USER;