Summary: You can encounter the "Incorrect PFS free space information" error when checking the database integrity using the DBCC CHECKDB command. This error usually occurs when there is an issue with the Page Free Space (PFS) pages. In this post, we'll see how to fix the page free space error in SQL Server. Also, we’ll mention an advanced SQL database repair tool to repair corrupt SQL database without any data loss.
Page is a primary storage unit in SQL Server. The server performs all the disk I/O operations at the page level. PFS (refers to Page Free Space) is one of the pages in the SQL database file that maintains the allocation status of every page. It records information on how much free space is available on each page. The SQL Server database engine uses the PFS pages to find which pages are allocated or free. The PFS records the allocation status, types of pages, and amount of free space available in the pages within the SQL database.
Sometimes, due to inconsistencies in the database, the information stored in the pages fails to match the actual type. This can lead to page-free space errors. The “Incorrect PFS free space information” error is one such error. This error occurs when you check database integrity using the DBCC CHECKDB command and the integrity check fails due to inconsistencies or corruption in the database. Below, we will explain the reasons for the “Incorrect PFS free space information” error in SQL Server and the solutions to fix this error.
Reasons for Incorrect PFS Free Space Information Error
Corruption in database (.MDF/.NDF) files can result in the “Incorrect PFS free space information” error. Corruption in database files can occur due to the reasons mentioned below:
- Sudden system shutdown
- Bugs in MS SQL Server
- Large or oversized database file
- MS SQL Server shuts down incorrectly or unexpectedly when the database is open
- Issue with the hard disk where database is saved
- System on which the data files are saved is virus-infected
Methods to Fix Incorrect PFS Free Space Information Error
The “Incorrect PFS free space information” error appears when the DBCC CHECKDB command detects corruption in the database. It displays the object name, table name, object ID, Partition ID, and Page ID, and recommends an appropriate repair option. So, you can move the data to new pages, restore the database from backup, or repair the database to try to resolve the error. Here’s how.
1 – Move Data to New Pages
The DBCC CHECKDB command displays the table name in which problematic PFS pages are located. You can try to copy the data from source table to a new table. This will assign new data pages and PFS entries. After this, delete the source table and rename the new table same as the source table. This can help in troubleshooting the error.
Note: This will work if you know the table name to which the problematic PFS pages belong.
2 – Restore the Database from Backup
If the database is corrupted, damaged, or unreadable, you can restore the database from the last updated backup. You can use the SQL Server Management Studio (SSMS) to restore the SQL database backup. Follow the steps mentioned below:
- Open the SSMS and go to the Object Explorer.
- Right-click Databases and select the Restore Database option.
- On the General page, under the Source section, select Device to specify the source and location of the backup sets to restore.
- In the Destination section, select the database name.
- In the Restore to field, you can leave the default as To the last backup taken.
- In the Backup sets to restore grid, click on the backups you need to restore.
- On the Options page, under Restore options section, you can select the required options. Then, click OK.
Alternatively, you can restore the database from backup by running the following T-SQL code:
RESTORE DATABASE [employee] FROM DISK = N'C:\Backup\employee.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
Here, ‘employee’ is the database name and ‘N’C:\Backup\employee.bak’ is the location where the backup is stored. The file is FILE 1 and STAT = 10 will display restoration progress every 10%.
3 – Repair Database using the DBCC CHECKDB Command
If you don’t have an updated backup, then you can use the DBCC CHECKDB command to repair the database. It can repair all the objects in corrupt SQL database, including index pages, PFS pages, and other allocation pages. You can run the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option as given below:
Note: Before using the command, it is suggested to take the backup of your SQL database.
DBCC CHECKDB ('employee', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
Note: Repairing SQL database with DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command reallocate the PFS pages or series of pages. It can cause data loss.
In case the DBCC CHECKDB command fails, then you can use a reliable third-party SQL repair tool, such as Stellar Repair for MS SQL. This tool can easily repair SQL database files (.mdf and .ndf) without any data loss. It can recover all the objects from the corrupt database, including pages, indexes, tables, and stored procedures. It then saves the recovered data to a new healthy database file (.mdf). This helps in resolving the issues occurred due to corruption in the database file. The tool supports repair and recovery of databases created in SQL Server 2022, 2019, 2017, 2016, and older versions.
Conclusion
The “Incorrect PFS free space information” error occurs when the SQL search engine fails to read the PFS pages. If the page free space pages in database files are corrupted, you can restore the database from the last backup. However, if the backup is not updated, then you can repair the database using the DBCC CHECKDB command. However, the best option to repair database without any data loss is by using an advanced SQL database repair tool, like Stellar Repair for MS SQL. It is an MVP-recommended tool that can repair both MDF and NDF files. It can recover all the data from the corrupt database file with complete precision.
