I have a SQL Server 2016 database and I configured in-memory table. I want to drop this configuration, then I drop that table. When I want to delete that file and filegroup, I got following error.

USE [InMem_Test]

Msg 41802, Level 16, State 1, Line 3 Cannot drop the last memory-optimized container 'InMemFile'. Msg 5042, Level 16, State 11, Line 5 The filegroup 'InMemFileGroup' cannot be removed because it is not empty.

2 Answers 2


Currently, the only way to remove Hekaton is to drop the whole database.

Probably not your first choice, but it doesn't look like Microsoft is willing to change course on that.


!!! Please use this method in case of corrupted database that contains memory optimized table (you can't use DBCC check on databases that contains memory optimized tables) !!!

WARNING: This is an unsupported operation. There have been reports of unrecoverable log corruption when using this.

It's a hack method and it's risky. Microsoft didn't verified this solution

Before doing anything take backup


  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 
        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]

Follow steps correctly for prevent log corruption

Step 4: Alter log file name must be done correctly Step 5: DBCC is Important to prevent corruption

Also for getting best result:

Don't use database while doing steps (Insert - Update - Changing Schema)

do CHECKPOINT to flush log to data file then shrink log file

you will never have log corruption issue

Don't forget:

Before using your database do this steps for checking issue:

  1. Insert or update 1 row in database
  3. Restart server

If database got online i think you did all steps correctly else Restore database from backup and forget about removing memory optimized filegroup

  • 6
    I work for Microsoft. Please never do this. Commented Jul 1, 2022 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.