Why PFS pages cannot be repaired

Last week there was a short discussion on Twitter about why PFS pages (damaged header, not individual PFS bytes) can’t be repaired (prompted by a #sqlhelp question about why they can’t be single-page restored, like other per-database allocation bitmaps). Just for the record, they can’t be fixed by automatic page repair in a mirror or AG either.

PFS pages occur every 8088 pages in every data file and store a byte of information about itself and the following 8087 pages. The most important piece of information it stores is whether a page is allocated (in use) or not. You can read more about PFS pages and the other per-database allocation bitmaps in this blog post.

So why can’t they be rebuilt by DBCC CHECKDB, when all the other per-database allocation bitmaps can?

The answer is that the is-this-page-allocated-or-not information is not duplicated anywhere else in the database, and it’s impossible to reconstruct it in all cases.

You might think that DBCC CHECKDB could work out which pages are allocated by inferring that state if a page is linked to in some way from another page that’s known to be allocated, and it could do that, except for the case of a heap with no nonclustered indexes.

If a heap has no nonclustered indexes, there is no other structure in the database that links to any of the data pages in the heap. Therefore, without the information in a PFS page, there’s no way to tell which of the pages contained in extents allocated to the heap are actually allocated or not. This is because SQL Server does not touch a page when it is deallocated, so there’s nothing on a page that indicates whether a page is currently allocated or has been deallocated.

So what if DBCC CHECKDB can tell that there are no such cases in the database?

The answer is that the algorithm to rebuild a PFS page given the links from other pages to pages covered by that PFS range is extremely complicated and would involve searching through the entire database, reading and processing all pages a second time *after* they’ve been repaired, looking for linkages to pages in the broken range. While it sounds technically possible, when scoping out writing such an algorithm back in 2001-2002, I quickly ran into run-time and complexity challenges that made the work entirely infeasible.

It’s also not possible to just mark all the pages allocated – because then allocation-order scans would come across potentially unformatted pages and fail. It would also break backups that use WITH CHECKSUM. Such an algorithm could be made to work (in the absence of heaps with no nonclustered indexes), but has the same problems as the algorithm above.

So – PFS pages can’t be repaired, and unless the database structure changes to mirror that allocation information in some way, I don’t see that changing at any point in the future.

Hope you found this interesting!

34 thoughts on “Why PFS pages cannot be repaired

  1. What would happen if the PFS page was written as if all pages were allocated?

    Then between 0 and 8087 pages would be un-usable, as SQL Server could not overwrite pages that were actually not in-use.
    But the database would be up-and-running.

    1. No – that doesn’t work either as then allocation order scans (e.g. select in a heap with no indexes) would come across unformatted pages and fail.

      1. Then what would happen if the PFS page was written as if all FORMATTED pages were allocated, and UNformatted pages not allocated?

        1. Firstly it’s difficult to tell what’s formatted vs. unformatted. A page could be from a previous use of that portion of the disk, with an old SQL Server data file page that happens to have the same allocation unit ID as an allocation unit in the current database – so that would break things. Secondly, it could be a deallocated page, so effectively reallocating it again would add data to the table/index, causing wrong results from a query – a much worse sin than having a query fail from encountering corruption.

    1. Yes – a full restore of at least the file containing the broken PFS, plus log backups to roll the file forward to the same point as the rest of the database.

  2. To be clear: we are talking about a single page (PFS) that got corrupted somehow (hardware failure?) and can’t be restored by itself.

    Therefore, considering the cause was a hardware failure and would not be replicated at a AG copy, I could :
    — switch the primary replica to one of the secondary replicas
    — backup the database from one of the healthy replicas
    — restore the database the original server after correcting the physical issue
    — switch the primary replica back to the original server
    — Life will be good again. :)

    Is this right?

    1. You could – however a much faster way would be to just correct the issue, restore the affected file from your existing backups and roll forward log backups. No need to take another full backup, as you should already have the necessary backups available.

      1. So I restored from backup, rolled forward log backups and corruption still there. Also checked my replicas and they have corruption as well. What else can I try?

        1. That says the corruption was in the database before the replicas were initialized, or something was corrupt in the log, which was then backed up and sent to the replicas.

          What corruption are you describing here?

          1. PFS corruption, database has been functioning well for over two weeks and started having issues today and find it strange that replicas are also corrupt.

            DBCC results for ‘MyDatabase’.
            Msg 8946, Level 16, State 2, Line 1
            Table error: Allocation page (9:16176) has invalid PFS_PAGE page header values. Type is 2. Check type, alloc unit ID and page ID on the page.
            Msg 8998, Level 16, State 2, Line 1
            Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 9 pages from (9:16176) to (9:24263). See other errors for cause.

  3. HI Paul,

    if Instant File Initialization is enabled, then it is possibility of data corruption as PFS pages are not zero initialized
    OR
    Allocation pages are always zero initialized whether IFI is enabled or not to avoid corruption.

    Regards

    1. Allocation bitmaps are formatted in memory with the correct bitmap bits set and written to disk as an 8k unbuffered write, there’s no zero initialization of that space in the data file. Instant file initialization is orthogonal to PFS page corruption.

  4. SQL 2016 – PFS error in myDB

    First run:
    DBCC CHECKALLOC (myDB,REPAIR_ALLOW_DATA_LOSS)
    DBCC results for ‘myDB’.
    Database error: Page (1:303905) is marked with the wrong type in PFS page (1:299256). PFS status 0x40 expected 0x60.
    The error has been repaired.
    CHECKALLOC found 1 allocation errors and 0 consistency errors in database ‘myDB’.

    Second run:
    DBCC CHECKALLOC (myDB,REPAIR_ALLOW_DATA_LOSS)
    Repair: The page (1:303905) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72058150719717376 (type Unknown).
    Page (1:303905) in database ID 10 is allocated in the SGAM (1:3) and PFS (1:299256), but was not allocated in any IAM. PFS flags ‘MIXED_EXT ALLOCATED 0_PCT_FULL’.
    The error has been repaired.
    CHECKALLOC found 1 allocation errors and 0 consistency errors in database ‘myDB’.

    Third run:
    DBCC CHECKALLOC (myDB)
    CHECKALLOC found 0 allocation errors and 0 consistency errors in database ‘myDB’.

    So, some PFS errors can be repaired ?
    In our case repair deleted one unused page, that didn’t belong to any object. Error was caused by disk full error. There were no heap tables in myDB.

    Regards

  5. Looks like this is what i am facing with my tempdb (SQL 2016 SP1 CU3.

    I get :

    Message
    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 9:63214; actual 0:0). It occurred during a read of page (9:63214) in database ID 2 at offset 0x0000001eddc000 in file ‘T:\MSSQL\Temp\temp8.ndf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    I run DBCC CHECKDB and there are no errors

    I drop the file then I get:

    Message
    Could not open File Control Block (FCB) for invalid file ID 9 in database ‘tempdb’. Verify the file location. Execute DBCC CHECKDB.

    Wieredest thing I have see so far in SQL Server

    1. DBCC CHECKDB can’t run allocation checks on tempdb (and hasn’t since 2000) so it won’t ever find PFS corruption errors. It can’t do this because a database snapshot can’t be created on tempdb, and when a snapshot can’t be created, and X database lock is the only way to run allocation checks, and tempdb can’t be exclusively locked.

      However, that page ID isn’t a PFS page (the page ID has to be exactly divisible by 8088 for it to be a PFS page).

      Did you just delete the file or did you do an ALTER DATABASE … REMOVE FILE? You shouldn’t get that error message if you dropped the file properly.

  6. hello,Paul!
    I have some questions about PFS page.
    1).Page 16 is the first page of sys.sysallocunits table, but in the PFS page, its state is 0x60(means MIXED_EXT ALLOCATED 0_PCT_FULL) I do not know why it is 0_PCT_FULL?? that means this page is empty? but actually ,page 16 contains 49 slot cnt.
    2).If my database’s PFS page is corrupt(such as become all 8k zero), I can copy a good PFS page from another MDF or somewhere else, and make its pageid,fileid correct, and then full the state byte with 0x44(means all the page was used), I know it will work on the index table ,and will not work on the heap, and now what will happen if I do a DBCC CHECKDB? I think maybe it will fixed the problem to the heap.

    1. 1) Because it’s a clustered index data page, and page free space in the PFS isn’t tracked for pages in a index
      2) No, you can’t do that. There is no algorithm that fixes PFS bytes correctly, otherwise I would have implemented it in the repair code for 2005.

      1. That means ,in all the clustered index page, in PFS state byte’s low 4 bits is always 0_PCT_FULL, only high 4 bits is meanningful

      2. you said “If a heap has no nonclustered indexes, there is no other structure in the database that links to any of the data pages in the heap. Therefore, without the information in a PFS page, there’s no way to tell which of the pages contained in extents allocated to the heap are actually allocated or not. This is because SQL Server does not touch a page when it is deallocated, so there’s nothing on a page that indicates whether a page is currently allocated or has been deallocated.“

        for the above sentence, I have some questions below:
        1. Does the IAM page of the heap can tell which extent is belong to the heap? Because to a heap, SQL Server scan its data page through the IAM page.
        2. I make all the PFS allocate, but the IAM page can tell which extent is belongs to the heap? so SQL Server will not read the page which is not in the IAM bitmap?

        1. 1) Yes, but the scan still needs the PFS page to tell which pages in an extent are actually allocated.
          2) No – heap scan uses the IAM to identify allocated extents, and then PFS to identify allocated pages.

          There is no algorithm which can correctly fix a PFS page that covers heap data pages.

          1. ok~thank you!
            one more question~
            what about the case that all the table is clustered index table? I ask you this question because in a case I make all the corrupt PFS all allocate, and then DBCC CHECKTABLE to that corrupt table, then the table become ok! can you tell me why? or help me

  7. Because that’s a pathological case where it’s possible to infer what’s allocated if the entire PFS interval is taken up by pages from indexes. DBCC CHECKDB doesn’t do that case as it’s not always possible to deterministically infer that everything in a PFS interval is from just indexes.

    1. thank you for your reply!
      Is there any article abot DBCC? I mean the detail about DBCC,such as how to check the metadata and data.

  8. Paul,

    You are always great

    If a heap has no nonclustered indexes, there is no other structure in the database that links to any of the data pages in the heap.

    Then how Heap pages are linked. I believed earlier it’s IAM pages that link together all heaps (+ Indexes) From that we can construed page allocated or not.

    Regards

  9. We’re getting PFS errors when restoring a SQL 2014 backup to SQL 2016. Is there a step we are missing in the backup restore that causes this or is this a known issue when migrating a 2014 database to 2016?

    So after receiving “Page x is marked with the wrong type in PFS page x” when restoring a vendor provided SQL 2014 database to our SQL 2016 server, we decided to try and see if we restored it to 2014 if the errors would go away. They did.

    We restored that vendor provided SQL 2014 database to a SQL 2014 server without issue. We ran the dbcc checks and it came out clean. No errors.

    DBCC results for ‘DBNameRemoved_0806’.
    CHECKDB found 0 allocation errors and 0 consistency errors in database ‘DBNameRemoved_0806’.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    We then made a backup of that same clean database, restored it on SQL 2016, and got the error again. Something about the conversion to SQL 2016 seems to be having issue.

    DBCC results for ‘DBNameRemoved_0806’.
    Msg 8948, Level 16, State 6, Line 1
    Database error: Page (1:22289) is marked with the wrong type in PFS page (1:16176). PFS status 0x40 expected 0x60.
    Msg 8948, Level 16, State 6, Line 1
    Database error: Page (1:22290) is marked with the wrong type in PFS page (1:16176). PFS status 0x40 expected 0x60.
    Msg 8948, Level 16, State 6, Line 1
    Database error: Page (1:22291) is marked with the wrong type in PFS page (1:16176). PFS status 0x40 expected 0x60.

    One thing I did notice is when I restored the original copy it was in SQL 2008 compatibility mode. Not sure that is part of the issue but I thought it odd and worth mentioning. Any ideas?

    1. Nothing to do with compatibility mode, but I’ve heard of this scenario happening so it must by a problem with the 2016 upgrade. I hesitate to say bug, as I don’t know what the cause is.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.