Removing an In-Memory OLTP File Group
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
-- Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE InMemoryOLTP
ADD FILEGROUP InMemoryOLTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
-- 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
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
ALTER DATABASE InMemoryOLTP REMOVE FILEGROUP InMemoryOLTPFileGroup
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!
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,