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


One can check integrity issues by using DBCC CHECKDB (“DatabaseName”) and unfortunately if DBCC CHECKDB returns some type of database corruption, then it can be solved by executing DBCC CHECKTABLE. According to BOL “DBCC CHECKTABLE: Checks the integrity of all the pages and structures that make up the table or indexed view.”
Most of the time it works fine for me at least, but last week it returned a different error and failed to fix it.

DBCC results for ‘MyTable’.

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:6294) in object ID 1325247776, index ID 1, partition ID 72057594860535808, alloc unit ID 72057594366853120 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
 

Error message clearly showing that there is no actual corruption for said page. You can call it just information that PFS (Page Free Space) entry has wrong calculation for free space in page. Though PFS showing that page is empty (0%), but in reality page is full (100%). And it is misleading free-space scanner, which finds it full when try to insert data.

You can live happily with this error, but if you have applied a job to execute DBCC CHECKDB then you will keep on receiving job failure notice.
 
How to resolve it:

Three methods, first, you should restore database from latest backup, which is error free. And second method is to create a replica of culprit table, insert data into it, delete existing (culprit table) and start enjoying new error free table.

(Once data copied to new table and verified, don’t forget to check further errors by executing DBCC CHECKDB.

Before trying above two methods first go for third one. i.e. DBCC PAGE

DBCC PAGE (‘YourDatabaseNameHere’,1, 6294, 1)
Advertisements

Posted on June 5, 2012, in Sql Server 2005, SQL Server Errors, SQL Server Log. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: