Consistency Errors due to MSG 8914 – PFS free space

Issue

While running DBCC CHECKDB() job on SQL 2008 R2 SP1 with SQL 2005 compatibility Level databases, it failed with the below DBCC results for ‘MESTEMPLATEDOCUMENTS’.

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:87991) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:88697) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91929) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91931) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91932) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

There are 0 rows in 1 pages for object “MESTEMPLATEDOCUMENTS”.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS'(object ID 1394416337).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (dbo.MESTEMPLATEDOCUMENTS’).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Resolution

After a Brief research found this solution from Paul Randall
http://www.sqlskills.com/blogs/paul/corruption-bug-that-people-are-hitting-msg-8914-pfs-free-space/

As solution didn’t work, I had to go ahead and do the repair

Implementing the repair on Dev or Test first to make sure the repair doesn’t cause any unintended results. Please take a Backup of the Database on which you would be working

Steps Followed:
1) Run DBCC Checktable(tablename) to verify if the issue is still occurring
2) If it does, query the table to see if you have any data in there to gauge the data loss after
the repair.
3) The table in question is empty
4) Change DB to “Single User Mode”
USE [master]
GO
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
5) Run the below to correct the errors
DBCC CHECKTABLE (MESTEMPLATEDOCUMENTS, REPAIR_ALLOW_DATA_LOSS )

Results are as follows

with All_ErrorMsgs DBCC results for ‘MESTEMPLATEDOCUMENTS’.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:87991) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:88697) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91929) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91931) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91932) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
There are 0 rows in 1 pages for object “MESTEMPLATEDOCUMENTS”.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS’ (object ID 1394416337).
CHECKTABLE fixed 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS’ (object ID 1394416337).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

6) Change DB back to Multi-User mode
USE [master]
GO
ALTER DATABASE [DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

7) To verify the results we can run the DBCC Checktable(tablename) command again and if no issues results will be as below

Consistency_Erorrs_and_PFS_space

Advertisements

About SQLKit

For almost 10 years supporting SQL versions starting 6.5 to current as an production SQL DBA and as Application admin. Interests or learning include Hadoop , MySQL, PostGRESql and SAP BASIS administration.
This entry was posted in Database Corruption, MSSQL. Bookmark the permalink.

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