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

udayarumilli_pfs

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.
  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

 

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

  1. It will reproduce the “PFS” error.
  2. To fix this execute the below two statements one after other.

 

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


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

 

 

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.
Posted in SQL Server DBA | Tagged , , , , , , , , , , | 3 Comments

Leave a Reply

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

Notify of
avatar
Sort by:   newest | oldest | most voted
Pankaj Joshi
Guest

Hi,

Thank you very much. This article helped me a lot.

Rockie
Guest

Awesome. Thank You very very Much! 🙂

Best regards

wpDiscuz