Although I have experience with In-Memory OLTP on SQL Server, I don’t anticipate I’ll be writing about it much. If you want to learn about that subject I highly recommend checking out Ned Otter’s blog. Much like Marcel Marceau is to miming, Ned has quietly become the master of documenting In-Memory OLTP. So much so I’ve even heard Microsoft’s Bob Ward refer to Ned’s posts.

Meanwhile, since you’ve decided to stick around this site, let me share some bad news about the files supporting your In-Memory OLTP.

At my current employment we utilize In-Memory OLTP to stage data for scheduled ETL processes, and it works very well. The performance is as wonderful as we would expect, so much so that we decided to try some memory-optimized tables on a different database.

Unfortunately, we noticed some issues with that new database, so as part of troubleshooting we figured we’d just get rid of the memory-optimized tables and go back to using on-disk tables. Create new tables in an on-disk ROW filegroup, move the data from memory-optimized tables to the new tables, drop the memory-optimized table, drop the memory-optimized file and. . .“I’m sorry Dave, I’m afraid I can’t do that.”

Yes, it turns out that even if you empty everything out of a memory-optimized data file, the file and the filegroup are stuck to your database forever. You’d know that if you read the documentation, and you wouldn’t know that if you didn’t read the documentation. Count me in the latter.

Side note: I have a new answer in case I’m ever asked “What’s the last thing you learned the hard way?”

Enough talk. Let me DEMO what I mean. Let’s create a database!

CREATE DATABASE InMemTest
GO

Let’s create a memory-optimized filegroup!

ALTER DATABASE InMemTest ADD FILEGROUP InMemFileGroup CONTAINS MEMORY_OPTIMIZED_DATA;
GO

Let’s create a memory-optimized file!

ALTER DATABASE InMemTest ADD FILE ( NAME = N'InMemFile1', FILENAME = N'C:\Data\InMemFile1' ) TO FILEGROUP InMemFileGroup;
GO

Now, I’d like to point out something that will probably have absolutely no bearing in your real world usage of SQL Server. If by chance you created this filegroup and file BUT DID NOT EVER CREATE ANY OBJECTS IN THE FILE (like a table) then you CAN in fact drop the file and the file group.

ALTER DATABASE InMemTest REMOVE FILE InMemFile1;
GO

The file ‘InMemfile1’ has been removed.

ALTER DATABASE InMemTest REMOVE FILEGROUP InMemFileGroup;
GO

The filegroup ‘InMemFileGroup’ has been removed.

Hooray! But this is real life, where files are created to have objects. Here’s what reality looks like when you create an object in that file.

Let’s create the file and filegroup again and try dropping them after creating a table.

ALTER DATABASE InMemTest ADD FILEGROUP InMemFileGroup CONTAINS MEMORY_OPTIMIZED_DATA;
GO

Commands completed successfully.

ALTER DATABASE InMemTest ADD FILE ( NAME = N'InMemFile1', FILENAME = N'C:\Data\InMemFile1' ) TO FILEGROUP InMemFileGroup;
GO

Commands completed successfully.

This RIGHT HERE is the point of no return.

USE InMemTest;

CREATE TABLE InMemTable (ID int NOT NULL PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

Commands completed successfully.

Now we have an empty table. And we’re gonna drop it like it’s hot.

DROP TABLE InMemTable;

Commands completed successfully.

So there’s nothing in the InMemFile1, and we should be able to drop it, right?

ALTER DATABASE InMemTest REMOVE FILE InMemFile1;
GO

Msg 41802, Level 16, State 1, Line 37
Cannot drop the last memory-optimized container ‘InMemFile1’.

Unbelievable. I tried my best to scour the interwebs for an answer as why this file can’t be dropped, and all I can find is “because Microsoft says so.”

Once you create 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…”

Like they said the Cubs ever winning the World Series was unlikely. Ahem.

That being said, there is one sliver of sunshine I would like to show you. The memory-optimized filegroup is truly forever and ever as long as your database still exists, but you CAN remove a memory-optimized file on one condition: you create another file in the filegroup to take it’s place. Watch this.

ALTER DATABASE InMemTest ADD FILE ( NAME = N'InMemFile2', FILENAME = N'C:\Data\InMemFile2' ) TO FILEGROUP InMemFileGroup;
GO

Commands completed successfully.

ALTER DATABASE InMemTest REMOVE FILE InMemFile1;
GO

The file ‘InMemFile1’ has been removed.

Huzzah! We made InMemFil1 go away, but only because we had a replacement file to take up the mantle of the immortal memory-optimized filegroup. And now InMemfile2 can’t be dropped. Good feelings gone.

I realize this doesn’t help much because your database is still stuck with memory-optimized files and filegroups like your iTunes playlist is stuck with U2’s “Songs of Innocence”. However, this does allow you move the supporting data file to a different file location if somehow that might help you.

Until Ned Otter comes up with a solution, this is the best we can do.