Sql-server – PFS Page corruption issue and question

corruptiondbcc-checkdbsql server

We recently encountered PFS page corruption in our production DB.  We are in the process of checking old backups to find which ones are clean, but I was wondering if a different process would work to shorten the time needed to fix this issue.  So far we have not encountered any client issues, and the DB remains usable, but obviously need to fix the problem.I know we can't repair the individual PFS pages, but would the below work?  

Msg 8948, Sev 16, State 6, Line 1 : Database error: Page (8:15390050) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390051) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390052) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390053) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390054) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]
Msg 8948, Sev 16, State 1, Line 1 : Database error: Page (8:15390055) is marked with the wrong type in PFS page (8:15383376). PFS status 0x40 expected 0x60. [SQLSTATE 42000]

I used DBCC Page and discovered and all 6 pages belong to 1 table, so I am hoping this will work so we don't need to restore from old backups, but everything I've read said its either restore from backup, or repair allow data loss. Also, what is the 0x60 status? using DBCC Page shows the 0x40 status is Allocated, so i am curious what the status of 0x60 is.
What do people think about this process? Would it work to clear the corruption?

  1. Copy all data to a backup table – New data pages should be assigned, which would mean the PFS page entries for these pages should be clean.
  2. Delete the source table – I assume this removes the bad PFS page entries since the table is no longer there…  But can SQL Server remove the errors at all if the PFS pages are corrupt, or would they remain even after deleting the table?
  3. Rename the table back to the source table – Hopefully this will solve the issue since new data pages and new PFS page entries were created in step 2.

Has anyone attempted this or heard of ways to deal with PFS page corruption without going back to an old backup?

enter image description here
enter image description here

Best Answer

See https://www.sqlskills.com/blogs/paul/pfs-corruption-after-upgrading-from-sql-server-2014/

If this database was upgraded some time from SQL Server 2014 or earlier, you may have had the problem before the upgrade, but it was undetected by CHECKDB.

You should contact Microsoft Support, which will probably tell you to run a DBCC CHECKDB (or CHECKALLOC) with REPAIR_ALLOW_DATA_LOSS (if that's the only error, it should not cause data loss; it would only fix the PFS type). You may need to run that twice to fix it; after the first run, you may still have an error such as:

Page (1:111) in database ID 11 is allocated in the SGAM (1:222) and PFS (1:333), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

Related Question