dbcc checkdb find the PCF number

Question

dbcc checkdb find the PCF number

TRACEY-320982

SSChampion

Points: 13653
More actions
January 24, 2008 at 7:51 am

#378134

I ran the following:
DBCC CHECKDB ('DBNAME') WITH DATA_PURITY
DBCC results for 'AllDocStreams'.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:26839) in object ID 181575685, index ID 1, partition ID 293374720802816, alloc unit ID 71788018805309440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Now before doing the repair_allow_data_loss i wanted to see what the PCT_FULL value was by looking at the Page
dbcc traceon(3604)
DBCC PAGE (1,26839,1)
(Keep telling me there no page for this)
So i looked at DBCC IND('DBNAME','AllDocStreams',1)
(Found the PagePID of 26839 and the IAMPID of 1577)
So i tried the DBCC PAGE (1,1577,1) still nothing.
I wanted to figure out what the error is telling me and why im getting it.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

DO NOT RUN Allow_data_loss. It's results are completely unpredictable and the storage engine team does not recommend it. They'd like to remove it.

Call PSS if this is a production database and let them work you through the issue.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

Here is my cent if it is possible for you.

Backup your database, and restore it in a different name, then see whether or not the error is gone.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

I took care of this on the MSDN HA/DR forum where it was cross-posted. See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2736150&SiteID=1.

To comment on the two replies:

1) The Storage Engine team doesn't want to remove the REPAIR_ALLOW_DATA_LOSS option. It will always be necessary as a way for people who don't have a backup to remove corruption. It's recommended as a last resort only. Too many people just run it straight away without evaluating their options - or restoring from perfectly good backups and thus avoiding data loss.

2) Backing up and restoring a corrupt database (no matter how benign the corruption) will NEVER get rid of the corruption. Backup and restore are purely physical read/write operations on the pages of the database. There are no changes made to them whatsoever. Same goes for attaching/detaching a corrupt database or shutting down SQL Server - all pointless.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

This was on a restore from SQL 2000 to SQL 2005 everything was checked then few weeks later appeared the corruption.

This is the first time i seen corruption on a database.

Removing indexes not worked so if was a true production i would be checking with the MS people.

Thanks

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

Removing indexes will have no effect. As I said in the MSDN forum thread, the PFS page tracks free space (among other things) for heaps and text data - nothing to do with indexes.

You can run repair to fix it if you want, or restore from your backups, or just create a clustered index (which will probably be better for you anyway) and the problem will go away.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005