How to remove In-memory optimized fie and file group

Question

How to remove In-memory optimized fie and file group

chonlatis.je

SSCommitted

Points: 1606
More actions
July 19, 2017 at 2:38 am

#402327

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

chonlatis.je SSCommitted Points: 1606 More actions · Answer 2

GilaMonster - Wednesday, July 19, 2017 3:24 AM
Have you removed all the objects in that filegroup?

There is no objects in that filegroup.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ahmad Raeiji Grasshopper Points: 12 More actions · Answer 4

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]