4

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

  • Per Paul Randal's blog post 0x60 implies the page is allocated AND is a mixed page. Do you get any errors when you execute SELECT * FROM tblname? If so, can you post the table definition as well as any indexes you have defined on it? – John Eisbrener Oct 1 '19 at 14:07
  • You may or may not have an issue copying the table, depending on the indexes used and the path taken. Does the GAM say it's allocated? Does the SGAM say it's a mixed extent? Really backups are the only true way to not lose any data, unless the log was corrupted as well. – Sean Gallardy - Retired User Oct 1 '19 at 14:18
  • did you encounter this issue after doing an upgrade or running some index maintenance ? What version of sql server are you running ? – Kin Shah Oct 1 '19 at 14:24
  • @JohnEisbrener The table is large, so I was avoiding a select *, but I did a few queries with a where clause and got no errors. I'll add table definition to the post and indexes. sean gallardy the GAM shows as allocated, but can't find info on the SGAM. There is no easy way to tell if a log is corrupted unless i restore it, so restoring a week only Full backup, then every log backup only to have it fail on the last ones is something i'd like to avoid. – DBA Greg14 Oct 1 '19 at 14:45
  • @KinShah we are on SQL server 2016 SP2 CU7 standard edition. no upgrades or db changes happened recently. – DBA Greg14 Oct 1 '19 at 14:50
1

You have damage to a couple of your PFS pages. This kind of corruption is irreparable.

Take a tail-log backup (if you can), then restore your full backup, all log backups followed by the tail-log backup that you took. You should be able to restore without data loss since you have a good set of backups.

You need to do some root cause analysis. Look in the windows event log, SAN logs, RAID logs, etc. This is typically an IO subsystem problem, you need to identify the cause or it may happen again.

| improve this answer | |
0

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'.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.