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? All 6 pages belong to 1 table, so I am hoping this would work...
PFS Page Output:
Allocation Status
GAM (1:15336960) = ALLOCATED SGAM (1:15336961) = NOT ALLOCATED
PFS (1:15383376) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:15336966) = CHANGED
ML (1:15336967) = NOT MIN_LOGGED
DBCC CheckDB Output:
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]
Msg 8954, Sev 16, State 1, Line 1 : CHECKDB found 6 allocation errors and 0 consistency errors not associated with any single object. [SQLSTATE 01000]
Msg 8989, Sev 16, State 1, Line 1 : CHECKDB found 6 allocation errors and 0 consistency errors in database 'XXXXXXXXX'. [SQLSTATE 01000]
Msg 8958, Sev 16, State 1, Line 1 : repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XXXXXX). [SQLSTATE 01000]
Which version of SQL Server are you using?
There's a blog post from Paul Randal regarding an issue when migrating from 2014 to 2016 or later versions, you might wanna take a look to it, looks like your case.
https://www.sqlskills.com/blogs/paul/pfs-corruption-after-upgrading-from-sql-server-2014/
There he says explictly that running checkdb with repair_allow_data_loss wont delete data, it would just fix the PFS state, it won't deallocate or delete anything.
October 1, 2019 at 4:12 pm
I'd look to Paul for the details here. You can email him this post and he may respond as well as use this in his weekly newsletter.
October 1, 2019 at 7:27 pm
we upgraded to sql 2016 from 2012 over a year ago, but the article you linked was helpful. I am going to test out running repair_allow_data_loss after backing up the table on an older restore and seeing if that solves the issue.
October 1, 2019 at 7:41 pm
If you have corruption in older backups, try restoring and executing DBCC CHECKDB with repair_allow_data_loss in another environment.
If it fix the issues great, run DBCC checkdb again to check for corruption, even after it finished and fixed the errors.
Also i'll advice the same as Sr. Steve, Paul always replies when he has the chance.
He can tell you if you can fix your database or if you have to move all your data to a new one.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply