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