In-Memory OLTP on SQL Server 2025 — create, use, and fully undeploy

/*
  Lab disk paths
  --------------
  Use C:\SQLLab\InMemoryOLTP when the host only has a C: drive.
  Use D:\SQLLab\InMemoryOLTP when a D: drive exists (search-and-replace the prefix below).
  Create Data, Log, and XTP folders first. The SQL Server service account needs Modify on that tree.
*/
USE master;
GO

IF DB_ID(N'InMemoryOLTP_Demo') IS NOT NULL
BEGIN
    ALTER DATABASE InMemoryOLTP_Demo
        SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;

    DROP DATABASE InMemoryOLTP_Demo;
END
GO

CREATE DATABASE InMemoryOLTP_Demo
ON PRIMARY
(
    NAME = N'InMemoryOLTP_Demo_Data',
    FILENAME = N'C:\SQLLab\InMemoryOLTP\Data\InMemoryOLTP_Demo.mdf',
    SIZE = 256MB,
    FILEGROWTH = 128MB
)
LOG ON
(
    NAME = N'InMemoryOLTP_Demo_Log',
    FILENAME = N'C:\SQLLab\InMemoryOLTP\Log\InMemoryOLTP_Demo.ldf',
    SIZE = 256MB,
    FILEGROWTH = 128MB
);
GO


ALTER DATABASE InMemoryOLTP_Demo
ADD FILEGROUP InMemoryOLTP_Demo_XTP
CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE InMemoryOLTP_Demo
ADD FILE
(
    NAME = N'InMemoryOLTP_Demo_XTP_Container',
    FILENAME = N'C:\SQLLab\InMemoryOLTP\XTP\XTPContainer'
)
TO FILEGROUP InMemoryOLTP_Demo_XTP;
GO


--vERIFY XTP CONTAINER

SELECT
    df.name,
    df.type_desc,
    df.physical_name,
    fg.name AS filegroup_name
FROM sys.database_files AS df
JOIN sys.filegroups AS fg
    ON df.data_space_id = fg.data_space_id;
GO

--MEMORY OPTIMIZED TABLE

USE InMemoryOLTP_Demo;
GO

CREATE TABLE dbo.CustomerMemory
(
    CustomerID BIGINT IDENTITY(1,1) NOT NULL,
    CustomerCode VARCHAR(30) NOT NULL,
    CustomerName VARCHAR(100) NOT NULL,
    CreatedDate DATETIME2(3) NOT NULL
        CONSTRAINT DF_CustomerMemory_CreatedDate
        DEFAULT SYSUTCDATETIME(),

    CONSTRAINT PK_CustomerMemory
        PRIMARY KEY NONCLUSTERED (CustomerID),

    INDEX IX_CustomerMemory_CustomerCode
        NONCLUSTERED (CustomerCode)
)
WITH
(
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA
);
GO

INSERT INTO dbo.CustomerMemory
(
    CustomerCode,
    CustomerName
)
VALUES
('CUST001', 'Customer One'),
('CUST002', 'Customer Two'),
('CUST003', 'Customer Three'),
('CUST004', 'Customer Four'),
('CUST005', 'Customer Five');
GO


--GENERATE WORKLOAD

DECLARE @i INT = 1;

WHILE @i <= 10000
BEGIN
    INSERT INTO dbo.CustomerMemory
    (
        CustomerCode,
        CustomerName
    )
    VALUES
    (
        CONCAT('CUST', RIGHT('000000' + CAST(@i AS VARCHAR(6)), 6)),
        CONCAT('Customer ', @i)
    );

    SET @i += 1;
END;
GO

--CHECK XTP MEMORY USAGE

SELECT
    DB_NAME(DB_ID()) AS database_name,
    memory_used_by_table_kb,
    memory_used_by_indexes_kb,
    memory_used_by_table_kb +
        memory_used_by_indexes_kb AS total_memory_used_kb
FROM sys.dm_db_xtp_table_memory_stats
 
GO

/*
  Lab disk paths
  --------------
  Use C:\SQLLab\InMemoryOLTP when the host only has a C: drive.
  Use D:\SQLLab\InMemoryOLTP when a D: drive exists (search-and-replace the prefix below).
  Backup files are written to the lab root. The SQL Server service account needs Modify on that folder.
*/
USE master;
GO

DROP TABLE IF EXISTS InMemoryOLTP_Demo.dbo.CustomerMemory

BACKUP DATABASE InMemoryOLTP_Demo
TO DISK = 'C:\SQLLab\InMemoryOLTP\InMemoryOLTP_Demonew.BAK'
WITH INIT, STATS = 10;
GO

BACKUP LOG InMemoryOLTP_Demo
TO DISK = 'C:\SQLLab\InMemoryOLTP\InMemoryOLTP_Demo_Undeploy.trn'
WITH INIT, STATS = 10;
GO

CHECKPOINT;

--wait for few mins and check the deployment_state_desc

SELECT
    deployment_state,
    deployment_state_desc,
    undeploy_lsn,
    start_of_log_lsn
FROM InMemoryOLTP_Demo.sys.dm_db_xtp_undeploy_status;

-- start_of_log_lsn > undeploy_lsn  Wait about a minute and check again

ALTER DATABASE InMemoryOLTP_Demo
REMOVE FILE InMemoryOLTP_Demo_XTP_Container;
GO

--while it is removing the file, open another window and issue this command
BACKUP LOG InMemoryOLTP_Demo
TO DISK = 'C:\SQLLab\InMemoryOLTP\InMemoryOLTP_Demo_Undeploy_02.trn'
WITH INIT, STATS = 10;
GO


--once the XTP file is removed then remove the filegroup as well

ALTER DATABASE InMemoryOLTP_Demo
REMOVE FILEGROUP InMemoryOLTP_Demo_XTP;
GO



USE InMemoryOLTP_Demo;
GO

SELECT
    deployment_state,
    deployment_state_desc,
    undeploy_lsn,
    start_of_log_lsn
FROM sys.dm_db_xtp_undeploy_status;
GO



BACKUP LOG InMemoryOLTP_Demo
TO DISK = 'C:\SQLLab\InMemoryOLTP\InMemoryOLTP_Demo_Undeploy.trn'
WITH INIT, STATS = 10;
GO

In-Memory OLTP, also known as Hekaton or XTP, keeps selected tables in memory and persists durable data through a special MEMORY_OPTIMIZED_DATA filegroup. That filegroup contains one or more directory-based containers rather than ordinary .ndf data files.

This article follows the two scripts from D:\Mac_bak\SQL_Helps\In_Memory_Hekaton:

• 001_create_db_FG_XTP_tables.sql creates a disposable database, XTP filegroup and container, durable memory-optimized table, sample workload, and memory report.
• 002_remove_files_FG_XTP.sql drops the table, advances the transaction log, removes the final XTP container, removes the filegroup, and checks undeploy status.

The two complete scripts appear above this article with Copy and Download controls.

Destructive lab warning: Script 001 drops InMemoryOLTP_Demo if it already exists. Script 002 permanently drops dbo.CustomerMemory, writes backup files with WITH INIT, and removes the XTP container and filegroup. Use a disposable SQL Server 2025 lab instance. Do not run either script unchanged against production.

What changed in SQL Server 2025

On SQL Server 2022 and earlier, dropping every memory-optimized object did not allow the final memory-optimized container and filegroup to be removed. The XTP engine remained deployed for the database.

SQL Server 2025, version 17.x, supports removing the last memory-optimized container and then its filegroup when all In-Memory OLTP objects have been removed. Completion is visible through sys.dm_db_xtp_undeploy_status: deployment_state = 0 means XTP is no longer deployed for the database.

This lab demonstrates that complete lifecycle:

1. Create a rowstore database.
2. Add a MEMORY_OPTIMIZED_DATA filegroup.
3. Add an XTP container.
4. Create and populate a durable memory-optimized table.
5. Read XTP memory usage.
6. Drop all memory-optimized objects.
7. Back up and advance the transaction log.
8. Remove the final container and filegroup.
9. Confirm that the XTP engine stopped.

Prerequisites

• SQL Server 2025 or later. Final-container removal does not apply to SQL Server 2022 or earlier.
• SSMS, Azure Data Studio, or sqlcmd.
• Permissions to create and drop a database, add and remove files and filegroups, run database and log backups, and issue CHECKPOINT.
• A disposable instance with no important database named InMemoryOLTP_Demo.
• No database snapshot for the demo database.
• Two query windows for the removal exercise.

The SQL Server service account needs Modify permission on the lab folders. The scripts default to:

C:\SQLLab\InMemoryOLTP\Data
C:\SQLLab\InMemoryOLTP\Log
C:\SQLLab\InMemoryOLTP\XTP

If the server has an appropriate D: drive, use D:\SQLLab\InMemoryOLTP instead and replace every path in both scripts. The XTP FILENAME is a directory path. SQL Server creates checkpoint and delta files inside it.

Part A: create the In-Memory OLTP lab

Open 001_create_db_FG_XTP_tables.sql, verify every path, connect to the disposable SQL Server 2025 instance, and execute the complete script.

Database creation

The script starts in master. If InMemoryOLTP_Demo exists, it switches the database to SINGLE_USER WITH ROLLBACK IMMEDIATE and drops it. It then creates a 256 MB primary file and 256 MB transaction log with fixed 128 MB growth.

The next statements add the special filegroup and its container:

ALTER DATABASE InMemoryOLTP_Demo
ADD FILEGROUP InMemoryOLTP_Demo_XTP
CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE InMemoryOLTP_Demo
ADD FILE
(
    NAME = N'InMemoryOLTP_Demo_XTP_Container',
    FILENAME = N'C:\SQLLab\InMemoryOLTP\XTP\XTPContainer'
)
TO FILEGROUP InMemoryOLTP_Demo_XTP;

Read the file and filegroup output

The first result joins sys.database_files with sys.filegroups.

Value to find	Expected result
InMemoryOLTP_Demo_Data	Row data file on PRIMARY
InMemoryOLTP_Demo_Log	Transaction log file
InMemoryOLTP_Demo_XTP_Container	Container associated with InMemoryOLTP_Demo_XTP
Container physical_name	The XTP directory configured in the script

The container can appear with a FILESTREAM-related type description because SQL Server uses the file-streaming storage infrastructure for durable XTP checkpoint files. Confirm the logical name carefully; script 002 uses that exact name in REMOVE FILE.

Memory-optimized table

The script creates dbo.CustomerMemory with:

• MEMORY_OPTIMIZED = ON
• DURABILITY = SCHEMA_AND_DATA
• A nonclustered primary key on CustomerID
• A nonclustered index on CustomerCode
• A UTC creation timestamp

SCHEMA_AND_DATA means the schema and rows survive restart and therefore require the XTP container. The table uses a nonclustered primary key because memory-optimized tables do not use a traditional clustered B-tree primary key.

The script loads five named rows and 10,000 generated rows. This creates enough activity to observe memory allocation and checkpoint files. It is not a performance benchmark.

Read the memory result

The final query reads sys.dm_db_xtp_table_memory_stats.

Column	Interpretation
database_name	Database whose XTP allocation is reported
memory_used_by_table_kb	Memory used for table rows
memory_used_by_indexes_kb	Memory used by memory-optimized indexes
total_memory_used_kb	Row and index allocation combined

Non-zero table and index values confirm that the object is loaded into the XTP engine. Memory consumption is workload- and schema-dependent; do not extrapolate production sizing from this small loop.

Part A is successful when the database, XTP filegroup, container, and table exist, the table contains approximately 10,005 rows, and XTP memory usage is greater than zero.

Part B: fully undeploy XTP

Open 002_remove_files_FG_XTP.sql only after Part A succeeds. Keep a second query window ready. Verify backup paths before execution.

The required order is:

1. Remove all memory-optimized tables, table types, and natively compiled modules.
2. Take the required backups and issue CHECKPOINT.
3. Remove the last XTP container.
4. Allow log truncation and the final XTP checkpoint to complete.
5. Remove the memory-optimized filegroup.
6. Confirm deployment_state = 0.

The demo contains one XTP table, so the script drops dbo.CustomerMemory. On a real database, inventory every memory-optimized table, memory-optimized table type, and natively compiled module before attempting container removal.

Why backups and CHECKPOINT matter

The script takes a full backup, a transaction-log backup, and issues CHECKPOINT. During final-container removal, SQL Server may need the active log start to advance past the undeploy LSN. Under FULL or BULK_LOGGED recovery, transaction-log backups can be required for that progress.

While the first window runs ALTER DATABASE ... REMOVE FILE, the second window can take the additional log backup shown in script 002. A long-running REMOVE FILE does not automatically mean the operation is hung. Read the undeploy status first.

How to read sys.dm_db_xtp_undeploy_status

SELECT
    deployment_state,
    deployment_state_desc,
    undeploy_lsn,
    start_of_log_lsn
FROM sys.dm_db_xtp_undeploy_status;

State	Meaning	Next action
0	XTP is not deployed, or undeploy completed	Container and filegroup removal is complete
1 or 2	XTP engine is deployed	Remove all XTP objects before starting undeploy
3	Last-container removal is waiting on checkpoint or log truncation	Run CHECKPOINT; under FULL recovery, take log backups and inspect log_reuse_wait_desc
4	Active log passed the undeploy LSN; final record is pending	Wait briefly; investigate replica apply lag if it remains
5	Waiting for the final XTP checkpoint	Run CHECKPOINT
6	Final-container removal was canceled or aborted	Run the REMOVE FILE statement again

Prefer deployment_state_desc if wording or numeric details differ on a later build.

When state 3 persists, check:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = N'InMemoryOLTP_Demo';

LOG_BACKUP calls for another log backup. ACTIVE_TRANSACTION requires investigation of the old transaction. Availability-replica or mirroring waits require the replica path to catch up. If log_reuse_wait_desc is NOTHING but state remains 3, another log backup and checkpoint may still be needed.

Final verification

After REMOVE FILE completes, script 002 removes InMemoryOLTP_Demo_XTP. Verify all four outcomes:

• dbo.CustomerMemory no longer exists.
• sys.database_files contains no XTP container.
• sys.filegroups contains no MEMORY_OPTIMIZED_DATA filegroup.
• sys.dm_db_xtp_undeploy_status.deployment_state equals 0.

The final transaction-log backup in script 002 is operational hygiene after undeploy. It is not the command that stops the XTP engine.

Production lessons from the lab

The lab demonstrates mechanics, not a production rollout plan. Before adopting In-Memory OLTP, validate table eligibility, transaction behavior, native compilation needs, memory capacity, durability, backup and restore operations, HA/DR compatibility, monitoring, and rollback. Benchmark the real workload rather than the insert loop in this demo.

For removal, take a recoverable backup before dropping data, inventory every XTP object, account for database snapshots and replicas, monitor log reuse, and allow enough time for the final checkpoint. The new SQL Server 2025 undeploy path removes a long-standing lifecycle limitation, but it still deserves a controlled maintenance window and a tested recovery plan.