In-Memory OLTP: create, use, and fully undeploy

1. Purpose and what changed in SQL Server 2025

In-Memory OLTP (also called Hekaton or XTP) stores selected tables in memory and persists them through a special filegroup of type MEMORY_OPTIMIZED_DATA. That filegroup holds one or more containers (folders on disk), not ordinary data files.

On SQL Server 2022 and earlier, after you dropped every memory-optimized object you still could not remove the last container and the memory-optimized filegroup. The In-Memory OLTP engine stayed loaded for that database.

Starting with SQL Server 2025 (17.x), you can remove every memory-optimized container and then the filegroup when no In-Memory objects remain. When that completes, sys.dm_db_xtp_undeploy_status.deployment_state is 0 and the XTP engine is stopped for the database.

2. Files in this folder

3. Prerequisites

1. SQL Server 2025 (17.x) or later. Last-container and filegroup removal is not supported on 2022 and earlier.
2. Permissions to create and drop databases, add/remove files and filegroups, back up databases and logs, and issue CHECKPOINT.
3. SQL Server Management Studio (SSMS), Azure Data Studio, or sqlcmd. Two query windows are useful for Part B.
4. The SQL Server service account must have Modify permission on the folders listed in section 4.
5. The demo database uses the default recovery model of model (usually FULL). Part B assumes you can take log backups so the log can truncate during undeploy.
6. Do not keep a database snapshot of InMemoryOLTP_Demo. Microsoft does not support container removal while snapshots exist. Drop snapshots first.

4. Folders and paths to create first

Not every machine has a D: drive. Choose the lab root from the number of local disks, then use that same root in both SQL scripts (every FILENAME and TO DISK path).

The SQL scripts are written with C:\SQLLab\InMemoryOLTP. If the snippet below selects D:, search-and-replace C:\SQLLab\InMemoryOLTP with D:\SQLLab\InMemoryOLTP in both .sql files before you run them. The SQL Server service account needs Modify permission on that folder tree.

Run this PowerShell on the SQL Server host. It picks D: when that drive exists, otherwise C:, creates the folders, and prints the root to copy into the scripts:

$labDrive = if (Test-Path -Path 'D:\') { 'D:' } else { 'C:' }
$labRoot  = Join-Path $labDrive 'SQLLab\InMemoryOLTP'

New-Item -ItemType Directory -Force -Path @(
    (Join-Path $labRoot 'Data'),
    (Join-Path $labRoot 'Log'),
    (Join-Path $labRoot 'XTP')
) | Out-Null

Write-Host "Lab root: $labRoot"
Write-Host "In both SQL scripts, every path must start with: $labRoot"

5. Part A — Create the database and memory-optimized table

Script: 001_create_db_FG_XTP_tables.sql

Run the entire script in one window connected to the SQL Server instance (context starts in master). Recommended isolation: do not run this against a shared instance that already has a database named InMemoryOLTP_Demo unless you intend to destroy it.

5.1 Drop an existing demo database (if present)

If InMemoryOLTP_Demo exists, the script sets it to SINGLE_USER with ROLLBACK IMMEDIATE and drops it. That disconnects other sessions and discards the previous lab.

5.2 Create the database (ROW store files only)

The database is created with a 256 MB primary file and 256 MB log, both growing by 128 MB. At this point there is no In-Memory filegroup yet.

5.3 Add the MEMORY_OPTIMIZED_DATA filegroup

ALTER DATABASE InMemoryOLTP_Demo
ADD FILEGROUP InMemoryOLTP_Demo_XTP
CONTAINS MEMORY_OPTIMIZED_DATA;

A database may have only one filegroup of this type. Adding it (and then a container) deploys the In-Memory OLTP engine for the database.

5.4 Add the memory-optimized container

ALTER DATABASE InMemoryOLTP_Demo
ADD FILE
(
    NAME = N'InMemoryOLTP_Demo_XTP_Container',
    FILENAME = N'C:\SQLLab\InMemoryOLTP\XTP\XTPContainer'  -- or D:\SQLLab\... if a D: drive exists
)
TO FILEGROUP InMemoryOLTP_Demo_XTP;

NAME is the logical name used later in ALTER DATABASE ... REMOVE FILE. FILENAME is the container folder on disk.

5.5 Verify files and filegroups

The script queries sys.database_files joined to sys.filegroups. Confirm you see:

• ROWS file InMemoryOLTP_Demo_Data on PRIMARY
• LOG file InMemoryOLTP_Demo_Log
• FILESTREAM / memory-optimized container InMemoryOLTP_Demo_XTP_Container on filegroup InMemoryOLTP_Demo_XTP (type_desc is typically FILESTREAM for the container)

Optional extra check after the filegroup exists:

USE InMemoryOLTP_Demo;
SELECT deployment_state, deployment_state_desc
FROM sys.dm_db_xtp_undeploy_status;
-- Expect deployment_state 1 or 2 while XTP is deployed.

5.6 Create the durable memory-optimized table

The script switches to InMemoryOLTP_Demo and creates:

• dbo.CustomerMemory with MEMORY_OPTIMIZED = ON
• DURABILITY = SCHEMA_AND_DATA (schema and data survive restart; needs the XTP container)
• Required nonclustered primary key on CustomerID
• Nonclustered index on CustomerCode

Memory-optimized tables cannot use a clustered B-tree primary key. A hash or nonclustered (range) index is required.

5.7 Load data and generate a small workload

1. Five named customers (CUST001 … CUST005).
2. A loop inserting 10,000 additional rows (CUST000001 …). This is enough to show checkpoint files and memory stats; it is not a performance benchmark.

5.8 Check XTP memory usage

The closing query reads sys.dm_db_xtp_table_memory_stats (table + index memory in KB). Non-zero values confirm the table is resident in the In-Memory engine.

6. Part B — Remove In-Memory objects, container, and filegroup

Script: 002_remove_files_FG_XTP.sql

Microsoft’s supported order is: confirm XTP is deployed → drop all In-Memory objects → remove all containers (last container starts undeploy, which can be long-running) → remove the filegroup → confirm deployment_state = 0.

6.1 Recommended session layout

1. Window 1: run script 002 (or step through it).
2. Window 2: keep ready for the extra BACKUP LOG while REMOVE FILE is running, as noted in the script comments.

Connect both windows to the same instance. Window 1 starts in master.

6.2 Drop every In-Memory OLTP object

The lab only created a table, so the script runs:

DROP TABLE IF EXISTS InMemoryOLTP_Demo.dbo.CustomerMemory;

On a real database you must also drop:

• All memory-optimized tables (sys.tables.is_memory_optimized = 1)
• All natively compiled modules (sys.all_sql_modules.uses_native_compilation = 1)
• All memory-optimized table types (sys.table_types.is_memory_optimized = 1)

Use the inventory queries in Microsoft’s article (also copied in section 8) before you remove files.

6.3 Backups before and during undeploy

Script 002 then:

1. Takes a full backup to <lab-root>\InMemoryOLTP_Demonew.BAK (WITH INIT overwrites that file).
2. Takes a log backup to InMemoryOLTP_Demo_Undeploy.trn.
3. Issues CHECKPOINT.

Under FULL recovery, log backups are often required so start_of_log_lsn can move past undeploy_lsn. That is why the script repeats log backups later.

6.4 Status check before REMOVE FILE

The script queries InMemoryOLTP_Demo.sys.dm_db_xtp_undeploy_status. At this moment XTP is still deployed (no container has been removed yet), so you typically still see state 1 or 2. The comment about waiting until start_of_log_lsn > undeploy_lsn applies after last-container removal has started (state 3), not before the first REMOVE FILE.

6.5 Remove the last memory-optimized container

ALTER DATABASE InMemoryOLTP_Demo
REMOVE FILE InMemoryOLTP_Demo_XTP_Container;

Removing the last container starts XTP undeploy. This statement can run for a long time. Do not treat a long wait as a hang until you have checked deployment_state (section 7).

If you cancel or abort this statement, undeploy may be partial. Microsoft documents deployment_state = 6 in that case: run REMOVE FILE again to finish.

While Window 1 is still waiting on REMOVE FILE, use Window 2 (as the script comments say):

BACKUP LOG InMemoryOLTP_Demo
TO DISK = 'C:\SQLLab\InMemoryOLTP\InMemoryOLTP_Demo_Undeploy_02.trn'  -- or D:\SQLLab\...
WITH INIT, STATS = 10;

You may also run CHECKPOINT in the demo database from Window 2. Repeat log backup / checkpoint if state stays at 3 (see section 7).

6.6 Remove the memory-optimized filegroup

Only after the container is gone:

ALTER DATABASE InMemoryOLTP_Demo
REMOVE FILEGROUP InMemoryOLTP_Demo_XTP;

If this fails, the container is still present or undeploy has not finished. Check files and deployment_state again.

6.7 Confirm the engine stopped

The script switches to the database and queries undeploy status again. Success is:

• deployment_state = 0
• No XTP container in sys.database_files
• No MEMORY_OPTIMIZED_DATA filegroup in sys.filegroups

A final log backup in the script (overwriting InMemoryOLTP_Demo_Undeploy.trn) is optional hygiene after undeploy; it is not what stops the engine.

7. How to read sys.dm_db_xtp_undeploy_status

Run this in the user database (or three-part name from master):

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

Always prefer deployment_state_desc on your build if the numeric mapping above ever differs slightly; the actions Microsoft documents for 3, 4, 5, and 6 are the ones this lab follows.

7.1 Completing last-container removal (state 3 in detail)

1. If state is 3 and undeploy_lsn is 0, run CHECKPOINT; in the database.
2. If state is 3 and undeploy_lsn is not 0, the log must truncate until start_of_log_lsn is greater than undeploy_lsn. Run CHECKPOINT, wait about a minute, and repeat. Under FULL or BULK_LOGGED recovery, also back up the log (script 002 already does this).
3. If state stays at 3, check why the log will not truncate:

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

   Common waits: LOG_BACKUP (take another log backup), ACTIVE_TRANSACTION (commit or kill the old transaction), AVAILABILITY_REPLICA, DATABASE_MIRRORING, and others documented under “Factors that can delay log truncation”. If log_reuse_wait_desc is NOTHING but state is still 3, Microsoft still recommends another transaction log backup; more than one may be required.
4. State 4: wait; check AG secondaries if it persists.
5. State 5: CHECKPOINT;
6. When the last XTP checkpoint finishes, REMOVE FILE should complete and state becomes 0. Then remove the filegroup.

8. Verification queries

8.1 Inventory In-Memory objects (before drop)

USE InMemoryOLTP_Demo;

SELECT object_id,
       OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       name AS table_name
FROM sys.tables
WHERE is_memory_optimized = 1;

SELECT object_id,
       OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS module_name
FROM sys.all_sql_modules
WHERE uses_native_compilation = 1;

SELECT SCHEMA_NAME(schema_id) AS type_schema_name,
       name AS type_name,
       OBJECT_NAME(type_table_object_id) AS type_table_name
FROM sys.table_types
WHERE is_memory_optimized = 1;

8.2 Files and filegroups

USE InMemoryOLTP_Demo;
SELECT df.name, df.type_desc, df.physical_name, fg.name AS filegroup_name, fg.type_desc AS fg_type
FROM sys.database_files AS df
LEFT JOIN sys.filegroups AS fg
    ON df.data_space_id = fg.data_space_id;

8.3 Row count after Part A

SELECT COUNT(*) AS customer_rows FROM InMemoryOLTP_Demo.dbo.CustomerMemory;

9. Notes, limitations, and cleanup

• SQL Server version: last container/filegroup removal requires 2025+. Creating In-Memory objects works on earlier versions that support In-Memory OLTP; undeploy of the last container does not.
• Database snapshots: drop them before REMOVE FILE on XTP containers.
• Always On: state 4 may wait until undeploy records apply on every replica.
• Canceled REMOVE FILE: state 6 — rerun the same REMOVE FILE.
• SSMS alternative: containers can be removed on the Files page of Database Properties; the filegroup on the Filegroups page. The T-SQL in script 002 is the repeatable lab path.
• Script 001 is not idempotent in a friendly way: it destroys any existing InMemoryOLTP_Demo.
• Workload loop: 10,000 single-row inserts are for visibility, not throughput testing. A native compiled procedure or batched insert would be used in a real benchmark.
• After the lab: DROP DATABASE InMemoryOLTP_Demo; from master if you no longer need it. Delete leftover backup files under your lab root (C:\SQLLab\InMemoryOLTP or D:\SQLLab\InMemoryOLTP) and empty XTP folders if Windows still shows them after a successful REMOVE FILE.

Suggested run order for someone receiving this folder

1. Confirm SQL Server 2025: SELECT @@VERSION;
2. Run the PowerShell in section 4. If it prints a D: root, replace C:\SQLLab\InMemoryOLTP with that root in both SQL scripts. Grant the service account access to the folders.
3. Open 001_create_db_FG_XTP_tables.sql, confirm every file path uses the same lab root, then execute.
4. Run the verification queries. Optionally take a full backup if you want a restore point with table data.
5. Open two SSMS windows. Run 002_remove_files_FG_XTP.sql in window 1; when REMOVE FILE is running, execute the extra BACKUP LOG in window 2.
6. If REMOVE FILE does not return, poll dm_db_xtp_undeploy_status and follow section 7.
7. Confirm state 0 and that the XTP filegroup is gone.

10. Microsoft references

• Memory-optimized container and filegroup removal (SQL Server 2025+): learn.microsoft.com — memory-optimized-container-filegroup-removal
• ALTER DATABASE file and filegroup options (ADD/REMOVE FILE and FILEGROUP)
• Factors that can delay log truncation (sys.databases.log_reuse_wait_desc)
• Checkpoint operation for memory-optimized tables
• DROP TABLE, DROP PROCEDURE, DROP TYPE

This guide documents the scripts as they exist in this folder and maps them to Microsoft’s SQL Server 2025 undeploy procedure. Database and backup file names are lab-specific. Disk paths default to C:\SQLLab\InMemoryOLTP; use D:\SQLLab\InMemoryOLTP when a D: drive exists.