Removing an In-Memory OLTP File Group

(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.)

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.

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:

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

3 Comments

  • 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

  • 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.

Leave a Reply to Chris Cancel reply

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

SQLpassion

Copyright © 2018 by SQLpassion e.U. · Imprint · Offerings · Academy · Contact · Data Protection · Go to Top