“Incorrect PFS free space information for page (1:103151) in object ID 1993058136, index ID 1, partition ID 72057594434682880, alloc unit ID 71906736119218176 (type LOB data).”
This error typically indicates a mismatch between the actual page allocation and what’s recorded in the Page Free Space (PFS) pages. It can affect database consistency checks and indicates underlying corruption in the database file. In this article, we will see how to fix the “incorrect PFS free space information for page” error in SQL Server.
Causes for Incorrect PFS Free Space Information Error in SQL Server
This error occurs when there is some issue with page free space (PFS) pages. The PFS pages can become inconsistent or corrupted due to one of the following reasons:
• Corruption in SQL Server database.
• Bugs in MS SQL Server.
• Bad sectors on the disk storing the database.
• System where the database is located is virus-infected.
• Sudden system shutdown.
Methods to Resolve Incorrect PFS Free Space Information Error in SQL Server
The error message usually displays the information related to the problematic pages. You can try the below troubleshooting methods to resolve the error.
Method 1: Move Data to New Pages
If the error message contains the name of table in which corrupt PFS pages are stored, you can try moving the data from that table to a new table. To copy the table data, run the SELECT command as shown below:
`SELECT * INTO newtablename FROM damagedtable`
Next, delete the original table to clear the problematic PFS entries. Then, rename the new table with the name of original table. To rename the table, use the ALTER TABLE command:
ALTER TABLE damagedtable
RENAME TO newtablename;
Method 2: Restore Database from Backup
If you have an updated database backup file, you can restore the backup file. It helps you replace corrupt pages metadata with the clean data. To restore the backup file, run the following query:
RESTORE DATABASE databasetest17
FROM DISK = 'Z:SQLServerBackupsdatabasetest13.bak' ;
It restores the entire database from backup, rather than specific pages.
Method 3: Perform Page-Level Restore
If the error message displays information related to a problem with pages, then you can perform Page-level Restore. This will help you to restore only the corrupt pages. However, it does not restore the Transaction logs, Allocation pages, Page 0 of all data files (the file boot page), Page 1:9 (the database boot page), and Full-text catalog.
You can use the SQL Server Management Studio (SSMS) to restore the pages or use the following T-SQL command:
Note: You must know the File ID where the page is located and the Page ID of corrupted page.
RESTORE DATABASE <database_name>
PAGE = '<file: page> [ ,... n ] ' [ ,... n ]
FROM <backup_device> [ ,... n ]
WITH NORECOVERY
Method 4: Use DBCC CHECKDB Command
You can also use the DBCC CHECKDB command to repair the corrupt SQL database. Here’s how to use the command with REPAIR_ALLOW_DATA_LOSS option:
• First, set the database in single-user mode by running the below command:
ALTER DATABASE databasetest17 SET SINGLE_USER
• Then, run the below command to repair the database:
DBCC CHECKDB (N ’ databasetest17 ’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
• After repairing the database, set it to multi-user mode by using the below command:
ALTER DATABASE databasetest17 SET MULTI_USER
_Note: Repairing the database with the REPAIR_ALLOW_DATA_LOSS option may deallocate pages or a series of pages. This may result in some data loss._
Method 5: Repair Database using a Professional SQL Repair Tool
To avoid data loss and quickly repair the corrupt SQL database, you can use a reliable SQL database repair tool. Stellar Repair for MS SQL is one such MVPs-recommended tool to repair corrupt SQL database of any size. It recovers all the objects, including pages, PFS, triggers, procedures, etc. from damaged MDF/NDF file with complete precision and integrity. It can help resolve corruption-related errors, including the page allocation errors. The tool is compatible with SQL Server 2019, 2017, and earlier versions. It supports both Windows and Linux operating systems.
Conclusion
The “Incorrect PFS Free Space Information” error is one of the errors related to page corruption in SQL Server. The most reliable method is to repair the corrupt NDF/MDF file by using a MS SQL repair tool, such as Stellar Repair for MS SQL. It can repair corrupt database files and recover all the objects, including PFS, pages, and indexes, without any data loss. The tool supports all SQL Server versions.