PFS corruption after upgrading from SQL Server 2014

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.


New live online training class in August: Planning and Implementing an Upgrade/Migration to SQL Server 2017

Continuing our series of live, online classes, Glenn will be delivering his new IEPUM2017: Immersion Event on Planning and Implementing an Upgrade/Migration to SQL Server 2017 in August! The class will be delivered live via WebEx on August 28-30 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end of the class.

Rather than have people try to watch a full day of training at their computer for one of more days, the class will run from 10am to 3pm PST each day, with two 90-minute teaching sessions, each followed by Q&A, and a lunch break. We chose to do this, and to spread the class over a few days, so the times work pretty well for those in the Americas, Africa, and Europe. We also realize that this is complex content, so want to give attendees time to digest each day’s material, plus extensive Q&A.

Here are some select quotes from prior attendees of Glenn’s in-person classes:

  • “Glenn is always so patient in answering my numerous questions.”
  • “Course information was very relevant since we are in the midst of migating our on-premise production environment to the cloud and upgrading to SQL Server 2016 or 2017.”
  • “Customers stories were a nice complement to the materials.”
  • “Great info for installing from the ground up.”

The modules covered will be:

  • Upgrade Planning
  • Hardware and Storage Selection
  • SQL Server 2017 Installation and Configuration
  • Upgrade Testing
  • Migration Planning
  • Production Migration Methods

The price of the class is US$699 (or US$599 for prior live, online attendees) and there’s also a combo price for all three new classes announced today.

You can get all the details here.

The class was also announced in our newsletter today, with a US$100 discount for those people who received that newsletter, valid through the end of June. All future live, online classes will always feature a discount for newsletter subscribers.

We decided to start teaching some live, online classes as we recognize that not everyone can travel to our in-person classes, or take that time away from work or family, or simply have travel budget as well as training budget. People also have different ways they learn, some preferring in-person training, some preferring recorded, online training, and some preferring live, online training.

We’ll be doing more of these so stay tuned for updates (and discounts through the newsletter).

We hope you can join us!