locked
Cant delete Full Text index and Full Text Catalogs RRS feed

  • Question

  • Hi ,
       We try to delete Full Text index for file stream table and Full Text Catalogs under Storage, both cant, and also if create new Full Text Catalogs with same file stream, showing errors.

    If i drop Full Text Catalogs, i get this error
    http://prntscr.com/iv6vtt

    When i run DBCC CHECKDB ('databasename'), i am getting below errors

    Msg 8948, Level 16, State 3, Line 1
    Database error: Page (1:1061) is marked with the wrong type in PFS page (1:1). PFS status 0x70 expected 0x60.
    Msg 8948, Level 16, State 3, Line 1
    Database error: Page (1:1073) is marked with the wrong type in PFS page (1:1). PFS status 0x70 expected 0x60.
    Msg 8906, Level 16, State 1, Line 1
    Page (1:1061) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 1
    Page (1:1062) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 1
    Page (1:1064) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 1
    Page (1:1065) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 1
    Page (1:1068) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 1
    Page (1:1069) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 1
    Page (1:1070) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 1
    Page (1:1071) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8906, Level 16, State 1, Line 1
    Page (1:1072) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
    Msg 8905, Level 16, State 1, Line 1
    Extent (1:8824) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
    Msg 8905, Level 16, State 1, Line 1
    Extent (1:8832) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

    Is there any option to repair database ? i try DBCC CHECKDB('databasename', REPAIR_REBUILD) , is it correct way ? it takes more days to complete when i google it. Or any other  way to repair database ?


    Because of above errors is it i cant delete Full Text index for file stream table and Full Text Catalogs under Storage ?

    Regards,
    Aravind
    Friday, March 23, 2018 8:20 AM

Answers

  • The minimum repair level is ALLOW_DATA_LOSS. If the data you lose is the full-text catalogs you want to get rid, that's alright, but it could just as well be critical data that is thrown away.

    If you want to go down that path, by all means take a backup of the datafirst and restore it somewhere. Run REPAIR_ALLOW_DATA_LOSS on the copy first and inspect what it throws away.

    Or restore from a clean backup.

    • Marked as answer by Aravindpanchu Tuesday, April 3, 2018 3:23 AM
    Friday, March 23, 2018 10:51 PM
  • I believe that DBCC reports what pages it tosses away. Basically, it throws away anything it can't piece together, which can just be one or two pages, or more or less the entire database. You will need make your investigation of data integretity on application level.

    • Marked as answer by Aravindpanchu Thursday, April 5, 2018 3:39 AM
    Tuesday, April 3, 2018 9:24 PM

All replies

  • There is definitely corruption in your database. Can you show me the last part of dbcc checkdb output it recommends minimum repair method and we may follow that.

    Do you have valid backups ?. Restoring from backup is clean, good solution


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, March 23, 2018 9:13 AM
  • Sure can , here i attach last part of dbcc checkdb output  in txt file , dropbox link is 

    https://www.dropbox.com/s/34aq29u6b8m6d7j/Database%20Error.txt?dl=0

    Pls check and reply  minimum method to repair db.


    Thanks


    Friday, March 23, 2018 9:19 AM
  • The minimum repair level is ALLOW_DATA_LOSS. If the data you lose is the full-text catalogs you want to get rid, that's alright, but it could just as well be critical data that is thrown away.

    If you want to go down that path, by all means take a backup of the datafirst and restore it somewhere. Run REPAIR_ALLOW_DATA_LOSS on the copy first and inspect what it throws away.

    Or restore from a clean backup.

    • Marked as answer by Aravindpanchu Tuesday, April 3, 2018 3:23 AM
    Friday, March 23, 2018 10:51 PM
  • yes, after repair with  ALLOW_DATA_LOSS , can delete and create newly and now works fine.
    But data may be loss means what, in terms what data going to loss ? we have file stream table, is binary file going delete ? but when count table before and after repair,count is same.



    Regards,
    Aravind

    Tuesday, April 3, 2018 3:27 AM
  • I believe that DBCC reports what pages it tosses away. Basically, it throws away anything it can't piece together, which can just be one or two pages, or more or less the entire database. You will need make your investigation of data integretity on application level.

    • Marked as answer by Aravindpanchu Thursday, April 5, 2018 3:39 AM
    Tuesday, April 3, 2018 9:24 PM