Search This Blog

Tuesday, July 27, 2010

Msg 8914, Level 16, State 1, Line 1 -> Incorrect PFS free space information for page

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


This was the error we were getting in the Docs table of one of the Sharepoint database .The Compatibility level was 80 and Build was 1399 (2005 RTM).

I tried a lot of things on it like :
-> I Rebuilt the clustered index with and without LOB_COMPACTION option .
-> DBCC page shows is fill factor 100
-> Changed the fill factor to 100 explicitly
-> Ran dbcc updateusage
-> changed the compatibility level to 90
-> changed the fill factor to to 99 ,50 etc
Nothing helped .The profiler did not show much (my intention was to know what checkdb is doing internally).

Finally I took the backup of the database and restored it as a test database .It did not give any errors .This means that actually its not a corruption .

On the restored database Ran DBCC checkdb with repair_allow_data_loss .
It fixed the issue without harming the data .Finally Ran the same on the Sharepoint database and it resolved the issue .

Hope this gives you the confidence to run the repair_allow_data_loss for this issue .
But remember , almost every time if you run it with repair_allow_data_loss you will end up loosing the data .So be careful .

This situation was AN EXCEPTION and you can safely use this option of checkdb.

Root cause :
Microsoft says that
the engine (just like OS does which giving pages to processes ) pre-allocates a set of data pages (say X) to the SPID which needs it and marks them as 100% full in PFS assuming that those pages will eventually get filled very soon.It does this to avoid frequently updating PFS page and improving performance.But later when the SPID completes its work in less pages (say X-Y) , these remaining pages are released .However, the remaining pages should be marked again as empty (0_PCT_FULL) which it does not do and hence DBCC CheckDB reports those errors (SQL 2000 silently use to fix it ).Repair_allow_data_loss will fix it with no data loss actually.

Regards
Abhay

3 comments:

Anonymous said...

Did you try DBCC UPDATEUSAGE before trying Repair Allow Data Loss??

Abhay said...

Sorry I missed publishing it ...yes ,I tried it. 3 times ...first time I saw some modification of pages .Second and third time no change in numbe rof pages ...

This is actually a kind of bug .The repair allow data loss will not do any data loss but just correct the page full value..

Raj said...

In most of the cases DBCC CHECKDB repair_allow_data_loss ends with some amount of data loss. I had also lost some data when i used it to repair sql database. It is interesting.

Thanks for sharing.