Back to Overview

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

  1. Neil M

    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

    Reply
  3. Chris

    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.

    Reply
  6. Balmukund Lakhani

    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.

    Reply
  7. Maulikkumar Patel

    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.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *

SQLpassion Performance Tuning Training Plan – Signup

Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan – you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.