Incorrect PFS free space information for page...

This article is a bit of a follow up to my why corruption shouldn't be that scary article. Actually, I guess for this example it should be called why failed consistency checks aren't that scary.

Anyway, here's a real life example:

So let's say consistency check (SQL 2005 btw) has failed with the following:

Incorrect PFS free space information for page (1:538) 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.

Oh oh, consistency check failures are bad; time to...

Now before I jump into what is going on here and what to do to fix it I did a quick search on the internet to see what advice people were giving. I found restarting SQL Server to be fairly common, one mentioned connecting via DAC, run some commands and the most common bit of advice I found was to completely restore the database!

They're all wrong, and this is why in my previous article I said to know exactly what you're up against when corruption occurs. You see in this case, it isn't actually corruption that is causing the consistency check to fail but a bug in an internal tracking algorithm.

This particular consistency error is one that can be fixed by running a checkdb with repair allow data loss. Whoa, what did you say there!? Surely you know you should never, ever run that command unless its completely a last resort?!

Well, that's true if we were dealing with corruption at page levels but in this case as no data is specifically involved it is fine (and the consistency check hasn't given us any additional errors). 

So lets fix this by running a repair with the following command:

DBCC CHECKDB ('MyBrokenDatabase', REPAIR_ALLOW_DATA_LOSS)

That isn't going to work, in order for a repair we have to put the database in single user mode.

Msg 7919, Level 16, State 3, Line 1
Repair statement not processed. Database needs to be in single user mode.

Here's the remaining commands:

ALTER DATABASE MyBrokenDatabase SET SINGLE_USER
GO

DBCC CHECKDB ('MyBrokenDatabase', REPAIR_ALLOW_DATA_LOSS)
GO

ALTER DATABASE MyBrokenDatabase SET MULTI_USER
GO

DBCC CHECKDB ('MyBrokenDatabase')

CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyBrokenDatabase'.

And there we have it. A database that has failed a consistency check that we haven't had to restore from backup and we didn't actually need to do any of this straight away. We could have waited for some acceptable downtime to avoid any resource usage by the process.

For another approach on dealing with this error check out this post from Dave Mason from his blog.