Friendly Reminder: Memory-optimized files and filegroups can never be forgotten

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.

4 thoughts on “Friendly Reminder: Memory-optimized files and filegroups can never be forgotten

  1. That was a good step by step information about in-memory optimized files.
    I followed the instructions and added new files as result transaction log file was reduced to the minimum size allowed my in-memory as there is a background process that hold a block at the end of transaction log file. to check bacground process run:

    select * from sys.dm_exec_requests where command = ‘XTP_OFFLINE_CKPT’

  2. 1- Delete memory optimized tables

    2- Detach Database

    3- Create new database with same files without memory optimized filegroup

    4- Modify database files and change it to detached database (mdf,ldf,ndf) files

    alter database test1 modify file (name=’test1′ , filename=’C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\test.mdf’)

    alter database test1 modify file (name=’test1_log’ , filename=’C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\test_log.ldf’)

    5- Now try to to repair database:

    alter database test1 set emergency

    alter database test1 set single_user with ROLLBACK IMMEDIATE;

    dbcc checkdb(test1,repair_allow_data_loss)

    alter database test1 set multi_user

    alter database test1 set online

    6- After successfully repair database remove memory optimized filegroup from database

    ALTER DATABASE [test1] REMOVE FILEGROUP [memory_optimized_filegroup_0]

  3. Followed the directions to move the file over but am getting this error when going to drop the file:

    Location: “sql\\ntdbms\\hekaton\\sqlhost\\sqlmin\\hkhostsqlstg.cpp”:1086
    Expression: cont
    SPID: 244
    Process ID: 13440
    Msg 3624, Level 20, State 1, Line 11
    A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
    Msg 596, Level 21, State 1, Line 10
    Cannot continue the execution because the session is in the kill state.
    Msg 0, Level 20, State 0, Line 10
    A severe error occurred on the current command. The results, if any, should be discarded.

Leave a Reply

Your email address will not be published.