I’m seeing reports from a few people of DBCC CHECKDB reporting PFS corruption after an upgrade from SQL Server 2014 to SQL Server 2016 or later. The symptoms are that you run DBCC CHECKDB after the upgrade and get output similar to this:
Msg 8948, Level 16, State 6, Line 5 Database error: Page (3:3863) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60. Msg 8948, Level 16, State 6, Line 5 Database error: Page (3:3864) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60. CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object. CHECKDB found 2 allocation errors and 0 consistency errors in database 'MyProdDB'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MyProdDB).
I’ve discussed with the SQL Server team and this is a known bug in SQL Server 2014.
The problem can occur if an ALTER INDEX … REORGANIZE is performed in a transaction and then rolled back, one of the affected extents can have some of its pages marked with the wrong PFS status. This state is valid in SQL Server 2014, but if one of the upgrade steps happens to move one of these pages, DBCC CHECKDB on the new version will complain with the errors above.
Note: this is not a bug in DBCC CHECKDB :-)
The fix for this issue is to run DBCC CHECKDB (yourdb, REPAIR_ALLOW_DATA_LOSS) and that will fix the PFS state. From anecdotal evidence, you might need to run repair twice. Repair will simply fix the PFS status, not deallocate/delete anything.
If you experience this issue, the SQL Server team requests that you contact CSS so they know how many people are hitting the issue and they may ask for access to the database to aid with developing a fix.
I’ll update this post when I get more information – at present (9/26/18) there is no fix available apart from running repair.
26 thoughts on “PFS corruption after upgrading from SQL Server 2014”
Will repair deallocate the page in question? Or will it just fix the PFS bits and not lose any data?
Just fix up the PFS – I updated the post to say that – thanks!
Paul thanks for sharing of this update.
Is there anyway to avoid the dbcc repair allow data loss?
I mean, isn’t that supposed to be a last resort?
Thought that rebuilding the index would fix this.
Thanks in advance!
No – from what I’ve heard and been told, rebuilding doesn’t work and repair is the solution. Remember that although repair is usually a last resort, this is a bug situation. Thanks
Just to clarify the update process, it means that I have 2014 databases (any patch version?), I back them up, restore them on SQL2016 or later, no matter with what db compatibility level I use, if the previous index maintenance conditions persisted, I might get the DBCC corruption warnings when running a full DBCC CHECKDB? If DBCC CHECKDB has not run yet on the upgraded databases, does the problem cause any visible problems with end-users, like data corruption issues, unexpected results, stackdumps etc.? So is it a major issue or “just” an internal problem that otherwise needs to be resolved? I am asking as we have hundreds of databases planned to be migrated over SQL2017 soon…
1) Correct. 2) Unknown, but shouldn’t lead to further corruption or data loss. 3) If present, it’ll cause consistency checking jobs to fail until it’s fixed, and a consistency check should ideally be run after any upgrade, as a just-in-case.
Hi Paul, Thanks so much for the post! So repair is the way to go and it is indeed a bug as I imagined. I am glad it’s sorted out for now and hopefully there will be a bug fix in 2014 soon.
A question regarding repair: running DBCC checkalloc repair runs a lot faster and fixes the corruption in two cycles run. Since this problem is an allocation issue, is it better and safer to run the repair with checkalloc? I need to run it during a go-live, and I would need the fastest but safest option. After a repair in two cycles, a checkalloc comes out clean and so does a full checkdb. What would be your suggestion?
Yes – as long ass that’s the only corruption, DBCC CHECKALLOC is sufficient.
We are facing the same PFS issue when upgrading from sql 2012 to sql 2017. and running it ist fixes PFS corruption and in IInd dbcc it repairs SGAM corruption.
We are not running Reorganize database. Please any insight into it
Not sure I understand your second sentence, but there’s no bug in 2012, it’s 2014. So you had real corruption.
Sir, from PFS pages can we find out table name to which it is allocated.
No – you need to look at the page that the error is about. See https://www.sqlskills.com/blogs/paul/finding-table-name-page-id/
I hit this same exact issue when migrating from 2012 to 2016.
Its not real corruption because I tested the database on another 2012 instance with no PFS corruption.
DBCC CHECKDB reports PFS corruption on SQL Server 2016 SP2 CU13 after restoring a SQL 2012 SP4(full patch level) database on it.
Any insights to this one?
Looks like the same bug. You’ll need to run repair to fix it.
We are moving SQL database from cloud hosting to on premises. To test this, we received database backup copy (SQL 2016 Version) from cloud vendor. We are getting same PFS errors as mentioned in this article for DBCC CheckDB run. Since repair_rebuild didn’t fix the issues, we tried REPAIR_ALLOW_DATA_LOSS option. However it deallocated the pages that had issues in DBCC run. When we tried to check objects associated with these pages using DBCC page after restoring database again, it’s showing Metadata: Object ID 0, index ID -1, partition ID 0 for these pages with no other contents apart from just BUFFER,PAGE HEADER,Allocation Status sections. You think if there would be any data loss after these pages are deallocated since these pages are not mapped to any objects?
No – if it’s the same bug, you should be fine as those pages are deallocated anyway.
Looks like I have hit this bug with the corruption happening at some stage between 23 August and 30 August. We only run a consistency check once a week.
However this is a different cause as the database has never been upgraded from any previous version and there have been no index rebuilds on the affected object. My only real question at this stage is what if anything could trigger an application run time error due to this corruption?
There are 4 pages reporting the PFS type error and on looking at each of them they are the same object, index and all page type 20. As a pre-allocated page would there be an error if SQL Server tried to use one or more of these pages as new rows are inserted to the table?
Which version are you on? If it’s 2012 or 2014, it’s likely the same bug and running repair will fix it.
This database was part of a migration from Sybase ADS to SQL Server. From the outset this was SQL Server 2017. The schema was created manually and the data was ported across via a set of BIML generated SSIS packages. Running repair does fix it but my concern is due to this happening on a database that started on SQL 2017 and has never been upgraded. The migration was completed July 2019 with this being the first time any corruption has been detected. We have not applied the repair in Live as yet as this will require an outage but this will be organised today. Hence my question can this corruption cause any run time errors?
Thank you for this blog it’s very helpful. I run this on 2 Production servers the first server run successful the second server kind the successful because the issue reoccur. Do you think it is possible the issue reoccur on the same DB’s?
Possibly – if there’s a different bug you’re hitting. You should contact Product Support for assistance.