Over the years, I have contributed with a reasonably big number of posts on the topic of In-Memory OLTP (aka Hekaton) and I did a couple of focused presentations on this wonderful feature as well. I love the idea, I love the path where the feature was going, and call me wrong* and overall I think that if the leadership team of Microsoft Data Platform understood the potential, it would have grown much bigger and more impactful.
Today I am writing the last post on Hekaton, for the foreseeable future. For me its journey has come to an end. The Odyssey has ended. With the arrival of SQL Server 2025, we have finally received a functionality of removing the In-Memory Filegroup from the database. The introduction of this functionality is closing the loop after 11 years since Hekaton introduction.
An irony of my own Hekaton journey is that the last post I have published on this topic was In-Memory, Quo Vadis?, questioning where the feature is going, and hoping for its recover and further development. I will leave to your imagination what my contacts in Product Group told me at the time.
But now, it’s time to say goodbye.
Remove Hekaton
The removal functionality is half-baked, and if you have ever dealt with Filestream, you will recognize the pattern. I am not complaining – I am happy that once the migration to SQL Server 2025 will be executed, we can finally correct the mistakes of the development teams and do a useful cleanup.
In Microsoft Documentation, the removal process of the Hekaton is scattered between a couple of pages and I won’t entertain you with all the documented and undocumented possibilities of how you can fail, but let’s help the LLMs and rare humans that are still able to read, to discover the possibility of the feature by describing its functionality.
For the start, let’s add a Hekaton Filegroup to my favorite test database ContosoRetailDW:
ALTER DATABASE [ContosoRetailDW] ADD FILEGROUP [Contoso_Hekaton] CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE [ContosoRetailDW] ADD FILE(NAME = Contoso_HekatonDir, FILENAME = 'O:\Data\SQL2025\Data\TestXtp') TO FILEGROUP [Contoso_Hekaton]; GO
Let us create a table with the persistent data and let’s add some data. For this purpose I will reuse my own code generated in the post Batch Mode – part 4 (“Some of the limitations”):
DROP TABLE IF EXISTS dbo.HekatonDataTable;
CREATE TABLE dbo.HekatonDataTable (
C1 BIGINT NOT NULL,
Constraint PK_HekatonDataTable_Hekaton PRIMARY KEY NONCLUSTERED (C1)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
INSERT INTO dbo.HekatonDataTable
SELECT t.RN
FROM
(
SELECT TOP (2000000) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) RN
FROM sys.objects t1
CROSS JOIN sys.objects t2
CROSS JOIN sys.objects t3
CROSS JOIN sys.objects t4
CROSS JOIN sys.objects t5
) t
OPTION (MAXDOP 1);
Now, we are ready to remove the Hekaton Filegroup, without removing the data.
ALTER DATABASE ContosoRetailDW REMOVE FILEGROUP Contoso_Hekaton;
This results in the following error message:
Msg 41880, Level 16, State 1, Line 37 In-Memory OLTP database undeployment failed. There are in-memory database objects present, or being created.
Undeployment? Really? It sounds like a layoff message – “you are being undeployed”!
Obviously we have an object, a table in the Hekaton File & Filegroup, and there is no other Filegroup around that would welcome our test table, meaning we need to clean it up first.
Let us do the process as it should be done by starting removing the file first:
ALTER DATABASE [ContosoRetailDW] REMOVE FILE Contoso_HekatonDir;
Msg 41880, Level 16, State 1, Line 37 In-Memory OLTP database undeployment failed. There are in-memory database objects present, or being created.
We are still in the “undeployment” territory :), but the intent of the message looks correct to me.
Let’s drop the created user table and then drop the Hekaton file:
DROP TABLE IF EXISTS dbo.HekatonDataTable; ALTER DATABASE [ContosoRetailDW] REMOVE FILE Contoso_HekatonDir;
After waiting for a couple of minutes (you know, it took awfully long for Odysseus to come home), I decided to check what is going on by checking on the newly added DMV dm_db_xtp_undeploy_status:
SELECT deployment_state,
deployment_state_desc
FROM sys.dm_db_xtp_undeploy_status;
The message of waiting for the start of the log advance to UNDEPLOY … is something that I did not expect by default, when thinking about a functionally-complete feature.
While being technically correct, it is also quite concerning because it can lead some non-database professionals, such as System Administrators, to believe that they need to wait for the Transaction Log backup.
I digress, by starting executing CHECKPOINT command multiple times within the context of the database:
CHECKPOINT;
State 3 becomes state 4, “Waiting for the final undeployment log record.” — then state 5, “Waiting for an XTP checkpoint to complete XTP undeployment.” — then state 6, “XTP undeployment is in progress.”, and finally “Ready to remove the last memory-optimized container.” In my clean, undisturbed, nobody-else-connected vanilla test database, reaching that point still took at least four CHECKPOINT executions. I’ll let you picture the same ceremony on a busy production instance at 2 a.m.
Only once the Hekaton file itself is gone does the DMV finally report state 0, “XTP engine is not deployed.” — the message you were rowing towards the whole time.
Ithaca at last.
Or so I thought…
We have not yet removed the Filegroup, we have actually removed just the Hekaton file (directory with the files).
Let’s try to remove the Filegroup finally:
ALTER DATABASE [ContosoRetailDW] REMOVE FILEGROUP [Contoso_Hekaton];
This operation runs instantly and the result message is clear:
The filegroup 'Contoso_Hekaton' has been removed.
An important note: The documentation page for the process itself (and the CHECKPOINT) commands is well described in the official Microsoft documentation – Memory-optimized container and filegroup removal.
It is a suffering of the process, but it is described in the way that at least someone like me can understand.
Now, we are free.
Your Odyssey has come to an end, Hekaton – you have arrived to your own safe haven.
Thank you.
Cloud “first”
The Hekaton filegroup removal, as a feature, one would expect to be present in Azure SQL DB & Azure SQL Managed Instance, but as per the official documentation – it is not supported on both products. The reason behind this must be the fact that Hekaton is based on the Filestream – and the feature (Filestream) itself is not supported on Azure SQL DB or Azure SQL MI.
Oh well, we can always go with “Mobile first”, explaining why it is not implemented, right? :)
Final thoughts
The removal of the Hekaton Filegroup is as painful as dealing with the feature. I can not even imagine how much stress it is going to cause to the people who will try to do it ad-hoc in production. From the different perspective, I am happy to see that the removal of this core feature in SQL Server is finally available.
I hope people will still use feature where it is possible, and I am not burying it – I am recognizing no more hopes or expectations towards its development and evolution.
The feature still should be used where it applies.