Yesterday evening I came across a very interesting behavior in SQL Server: a customer has told me that they have some problems by removing a file group that was used for In-Memory OLTP. Unfortunately that is NOT possible! You can’t remove an In-Memory OLTP file group!
A simple example
Let’s have now a look on a simple example that proves to you that this is not possible. Let’s create in the first step a new database and prepare it for In-Memory OLTP by adding a new file group that contains memory optimized data.
-- Create new database CREATE DATABASE InMemoryOLTP GO -- Add MEMORY_OPTIMIZED_DATA filegroup to the database. ALTER DATABASE InMemoryOLTP ADD FILEGROUP InMemoryOLTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA GO USE InMemoryOLTP GO -- Add a new file to the previously created file group ALTER DATABASE InMemoryOLTP ADD FILE ( NAME = 'InMemoryOLTPContainer', FILENAME = '/var/opt/mssql/data/InMemoryOLTPContainer' ) TO FILEGROUP InMemoryOLTPFileGroup GO
Imagine now that you want to remove again this file group, because you don’t need the In-Memory OLTP functionality in your database anymore:
ALTER DATABASE InMemoryOLTP REMOVE FILE InMemoryOLTPContainer GO ALTER DATABASE InMemoryOLTP REMOVE FILEGROUP InMemoryOLTPFileGroup GO
If you try to remove the file or even the file group, you will get a nice error message by SQL Server, which informs you that this is not possible:
Msg 41802, Level 16, State 1, Line 1 Cannot drop the last memory-optimized container ‘InMemoryOLTPContainer’.
Msg 5042, Level 16, State 11, Line 3 The filegroup ‘InMemoryOLTPFileGroup’ cannot be removed because it is not empty.
I have reproduced this behavior on SQL Server 2019 CTP 2.3 in a Linux based Container. So this limitation also applies to the latest, greated release of SQL Server. To be on the fair side, that behavior is also very well documented in Books Online:
“Once you use a memory-optimized filegroup, you can only remove it by dropping the database. In a production environment, it is unlikely that you will need to remove the memory-optimized filegroup.”
It is unlikely that you will need to remove the memory-optimized file group? It seems that the developers of SQL Server are really, really convinced by the software they are creating. But to be honest: why on earth is it not possible to remove such a file group? I don’t want to drop and recreate the whole database just to get rid of that file group!
Summary
We are living in the year 2019, and In-Memory OLTP was first released back in 2014 – 5 years ago! Another reason why I not, or even eventually not recommending In-Memory OLTP for the broad range of database workloads that I see…
Thanks for your time,
-Klaus
7 thoughts on “Removing an In-Memory OLTP File Group”
Hi Klaus
Your post is very timely. We have a memory optimised filegroup, comprising two folders, one of which is on a volatile drive. Is it possible to relocate the folder on the volatile drive e.g. by altering the database to change the folder, taking the database offline, copying the files in the folder and then bringing the database online?
Thanks
Always good to provide the feedback link so that the people who agree with you can pass it on to Microsoft.
https://feedback.azure.com/forums/908035-sql-server/suggestions/32896801-in-memory-oltp-allow-removal-of-filegroup-for-me
I have just been hit badly by this.
We have numerous production databases running in AWS that we need to migrate to Multi-AZ for HA and failover. Multi-AZ only supports databases without OLTP filegroups. All our databases have such filegroups – although they are completely unused.
There is no way to delete them – so our only solution is to backup, recreate and restore – something that is risky and creates a *huge* amount of downtime.
Klaus :-
It appears that the issue of not been able to remove empty MEMORY_OPTIMIZED_DATA filegroups has been addressed.
I tested against :-
Version :- Microsoft SQL Server 2019 (CTP3.2) – 15.0.1800.32 (X64)
Release Date :- 2019-July-17
Documented here :-
https://learningintheopen.org/2019/09/29/sql-server-database-management-v2019-ctp/
Family:
Spoke way too soon, as always.
What triggers the no-return is creating a memory optimized object in the targeted database.
Sorry,
Daniel Adeniji
Microsoft SQL Server 2019 (RC1) – 15.0.1900.25 (X64)
Aug 16 2019 14:20:53
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 18363: ) (Hypervisor)
It works fine. Output
The file ‘InMemoryOLTPContainer’ has been removed.
The filegroup ‘InMemoryOLTPFileGroup’ has been removed.
Hi klaus,
Is their any way to get rid of In Memory Filegroup?
We Dropped Database ,Created New DB and Restore From Full Backup But I cannot Get Rid of In Memory Filegroup.