How to remove In-memory optimized fie and file group

  • Can drop these file without drop Database? because it production DB.

  • Have you removed all the objects in that filegroup?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Wednesday, July 19, 2017 3:24 AM

    Have you removed all the objects in that filegroup?

    There is no objects in that filegroup.

  • You can't remove those once created. When teaching classes on in-memory as an optimization technique, I always warn against this. Those are a permanent part of that database until some update from Microsoft allows you to remove them. Minimize & shrink them, never use them, and it'll be fine, just a pain. If you absolutely must remove them, then you need to create a new database and migrate every object over into it.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software


    1- Delete memory optimized tables

    2- Detach Database

    3- Create new database with same files without memory optimized filegroup

    4- Modify database files and change it to detached database (mdf,ldf,ndf) files

    alter database test1 modify file (name='test1' , filename='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\test.mdf')

    alter database test1 modify file (name='test1_log' , filename='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\test_log.ldf')

    5- Now try to to repair database:

    alter database test1 set emergency

    alter database test1 set single_user with ROLLBACK IMMEDIATE;

    dbcc checkdb(test1,repair_allow_data_loss)

    alter database test1 set multi_user

    alter database test1 set online

    6- After successfully repair database remove memory optimized filegroup from database

    ALTER DATABASE [test1] REMOVE FILEGROUP [memory_optimized_filegroup_0]

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply