Advice for "Incorrect PFS free space information"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Advice for "Incorrect PFS free space information"

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2011-07-25 : 07:54:11

One of our databases is giving this error:


DBCC CHECKDB (MyDb) WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:218) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (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:219) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (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:220) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (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:221) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'MyDb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MyDb).

The system seem to work fine, we're not getting any errors. I'm really reluctant to run repair_allow_data_loss because this one is a very critical database. I appreciate any advice on this.

Using Microsoft SQL Server 2008 (SP2) - 10.0.4279.0 (X64) Mar 10 2011 15:03:15 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)

Database was 2005, converted to 2008 on new installation.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-25 : 10:02:16

Run CheckDB with repair allow data loss. If these are the only errors it won't lose data. It's just that the type of repair can't be done in the lower repair levels.

--
Gail Shaw
SQL Server MVP

markwillium
Starting Member

11 Posts

Posted - 2011-07-26 : 01:52:43

Take a backup of your database and restored it as a test database, if it does not gives any error that means there is no any corruption in the database. Run DBCC CHECKDB with repair_allow_data_loss on the restored database.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-26 : 03:39:55

quote:
Originally posted by markwillium

Take a backup of your database and restored it as a test database, if it does not gives any error that means there is no any corruption in the database.

It's quite possible to have a corrupt database restore without error.

--
Gail Shaw
SQL Server MVP

kensai
Posting Yak Master

172 Posts

Posted - 2011-07-26 : 04:43:26

quote:
Originally posted by GilaMonster

Run CheckDB with repair allow data loss. If these are the only errors it won't lose data. It's just that the type of repair can't be done in the lower repair levels.

--
Gail Shaw
SQL Server MVP

Could you elaborate "if these are the only errors"? If you mean dbcc results yes that's the whole result in my post. Or should I check for something else before running repair?

Also what do you think of this solution depicted here:

DBCC PAGE ('DATABASENAME', 1, <pageid>, 1)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-26 : 04:55:47

If the only errors that CheckDB returns are "Incorrect PFS free space information" then a repair_allow_data_loss will fix things without data loss.

The solution given on that thread (by Paul Randal, the guy who wrote checkDB) is the same as the one I gave you.

--
Gail Shaw
SQL Server MVP

markwillium
Starting Member

11 Posts

Posted - 2011-07-26 : 07:07:26

quote:
Originally posted by GilaMonster

It's quite possible to have a corrupt database restore without error.

--
Gail Shaw
SQL Server MVP

Yes sir, you are right. Thanks for the correction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources