can we remove Memory Optimized file group

_{Wednesday, April 8, 2020 3:17 PM}

I tried to remove memory optimized filegroup from database.  i checked on sql server 2019 (15.0.2070.41) developer edition.

    Alter database InMemoryCheckpoint 
    Remove file InMemoryCheckpointDF

    Alter database InMemoryCheckpoint 
    Remove filegroup InMemoryCheckpointDF

_{Wednesday, April 8, 2020 3:39 PM ✅Answered}

Based on "The Memory Optimized Filegroup" from Microsoft, you cannot remove a Memory-Optimized Filegroup without dropping the database:

The following limitations apply to a memory-optimized filegroup:

• Once you use a memory-optimized filegroup, you can only remove it by dropping the database. In a production environment, it is unlikely that you will need to remove the memory-optimized filegroup.
• You cannot drop a non-empty container or move data and delta file pairs to another container in the memory-optimized filegroup.

A Fan of SSIS, SSRS and SSAS

_{Thursday, April 9, 2020 1:28 AM}

I there any way to set off memory optimized option in sql server ?

_{Thursday, April 9, 2020 4:18 AM}

Hi mearpit015,

>>>can we remove Memory Optimized file group
you can't remove it unless delete the database.
However, if you create this file group and file but never create any objects (such as tables) in the file, you can remove the file group.

>>>I there any way to set off memory optimized option in sql server ?
you can't disable the Memory-Optimized  in Memory-Optimized Table, If you do not want to use the memory-optimized tables, the only thing you can do is move the data from the memory-optimized table to a new table (disk table), and then drop the memory-optimized table. But you can’t remove  the Memory Optimized file group even if you clear everything in the file.

Best Regards,
Cris

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.

_{Tuesday, April 14, 2020 7:02 AM}

Hi mearpit015,

Was your issue resolved?
If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
In addition, if you have another questions, please feel free to ask.
Thanks for your contribution.

Best regards,
Cris

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.