In Memory Database Remove File and FileGroup

Question

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]
GO
ALTER DATABASE [InMem_Test]  REMOVE FILE [InMemFile]
GO
ALTER DATABASE [InMem_Test] REMOVE FILEGROUP [InMemFileGroup]
GO

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.

Answer 1

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.

Answer 2

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

then

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]
   

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
2. Do CHECKPOINT
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