none
DBCC CHECKDB Pages is marked with the wrong type in PFS page do DBCC CHECKDB ([MSDB], REPAIR_ALLOW_DATA_LOSS) but appear new SGAM and PFS errors RRS feed

  • Question

  • i do DBCC CHECKDB after upgrade sql server from 2012 vesion to 2017 then find the database error  as below:

    Page (1:214377) is marked with the wrong type in PFS page

    then i run DBCC CHECKDB ([MSDB], REPAIR_ALLOW_DATA_LOSS)

    so the first  error is repaired and the new error appear as bellow:

    Msg 8906, Level 16, State 1, Line 4
    Page (1:214377) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 4
    Page (1:214378) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 4
    Page (1:214379) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 4
    Page (1:214380) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 4
    Page (1:214381) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 4
    Page (1:214382) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 4
    Page (1:214383) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.

    so what should i do guys?is the DBCC CHECKDB ([MSDB], REPAIR_ALLOW_DATA_LOSS) wrong or not?im so worry to lost data and corruption in database.!!!!




    Wednesday, December 4, 2019 1:24 PM

All replies

  • Sounds like problem in only one msdb extent.

    What's results from DBCC CHECKALLOC?

    Also apply last CU.
    Wednesday, December 4, 2019 4:19 PM
  • Hi Andre,

    I did DBCC CHECKALLOC and see the same error.

    DBCC CHECKALLOC

    Msg 8906, Level 16, State 1, Line 12
    Page (1:214377) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.

    CHECKALLOC found 7 allocation errors and 0 consistency errors not associated with any single object.

    Wednesday, December 4, 2019 6:18 PM
  • First, take a backup for this database.

    Second, run DBCC CHECKALLOC('your_database', REPAIR_REBUILD) in single user mode.

    Then, run DBCC CHECKDB again.

    Wednesday, December 4, 2019 6:33 PM
  • Next Error After your Script:

    Page (1:214377) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
            The repair level on the DBCC statement caused this repair to be bypassed.

    Wednesday, December 4, 2019 8:35 PM
  • I had the same issue when I migrated SQL 2014 database to SQL 2016. 

    Below is the steps I used and it cleared the error after I run it 3x.

    1. Change database to SINGE_USER mode
    2. Run DBCC CHECKDB (msdb, REPAIR_ALLOW_DATA_LOSS) multiple times until error clears.
    3. Change database to MULTI_USER mode

    You won't lose any data.

    Wednesday, December 4, 2019 9:09 PM
  • Hi Samaneh Golnavaz,

    Did you have backup file? Restoring the database through known clean backup helps in removing the error message. Please also read  Fix SQL server error 8906 with a trusted solution  to get more information.

    I also find a similar thread, hope it could help you.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 5, 2019 8:04 AM
  • I had the same issue when I migrated SQL 2014 database to SQL 2016. 

    Below is the steps I used and it cleared the error after I run it 3x.

    1. Change database to SINGE_USER mode
    2. Run DBCC CHECKDB (msdb, REPAIR_ALLOW_DATA_LOSS) multiple times until error clears.
    3. Change database to MULTI_USER mode

    You won't lose any data.

    That last bit is a very dangerous remark.  Whether or not a repair using REPAIR_ALLOW_DATA_LOSS will result in data loss will depend on the situation. It is all down to what type of corruption you have in the database.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, December 5, 2019 10:34 AM
  • Hi Tiborki

    i resote the back up of database in another engine to test the solution and i did repair with

    DBCC CHECKDB (msdb, REPAIR_ALLOW_DATA_LOSS)

    some repair but see another error:

    database ID 9 is allocated in the SGAM (1:3) and PFS (1:210288), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL

    you are right the first command can be so dangerous

    so i dont know what should i do!!!!




    Thursday, December 5, 2019 8:58 PM
  • Restore from a clean backup is the preferred way to handle this.

    Anther option is to rebuild the system databases, you can do that on a test SQL server with the same version level and the copy just msdb database files to the production server. But you'll lose everything in the msdb, of course.

    A third option is to keep doing repair with allow data loss until is comes out clean. But you don't know the state of msdb, since allow data loss means it just throws away what it doesn't consider healthy.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, December 6, 2019 12:59 PM