Getting rid of an In-Memory OLTP filegroup you can't delete: four ways out

In a previous post I reproduced a nasty leftover: a database that once used an In-Memory OLTP (memory-optimized) table, migrated back to disk and dropped it, but kept the MEMORY_OPTIMIZED_DATA filegroup it required — and an XTP_OFFLINE_CKPT background thread quietly burning CPU with zero in-memory tables. The punchline was grim: you cannot remove that filegroup in place. Every ALTER fails (Msg 41802, Msg 5042, and on SQL Server 2025 a hung “undeployment” ending in Msg 41879).

That post proved the problem. This one is the part you actually need when you’re standing in it: how do you get rid of the thing? There are no in-memory tables anymore — just the filegroup and its CPU tax. I worked the removal from four angles in a reproducible Docker harness, each starting from that exact orphaned state. Here’s each one, with the receipts.

The starting database isn’t a toy SELECT INTO either — it has two tables with a foreign key, an index, a view and a stored procedure, so “rebuild it” means recreate every object, the way it really is in the field.

Scenario A — the honest rebuild + rename swap

Since no ALTER removes the filegroup, the baseline fix is to build a new database that never had it and move everything across:

-- A clean database: PRIMARY + LOG only. No CONTAINS MEMORY_OPTIMIZED_DATA.
CREATE DATABASE [AppClean]
ON PRIMARY (NAME = N'AppClean', FILENAME = N'.../AppClean.mdf', ...)
LOG ON     (NAME = N'AppClean_log', FILENAME = N'.../AppClean_log.ldf', ...);

Then recreate every object by script — not just the tables: the foreign key, the index, the view, the procedure. Copy the data parent-first (FK-safe), and verify the new database is XTP-free:

SELECT COUNT(*) AS fx_filegroups FROM AppClean.sys.filegroups WHERE type = 'FX';   -- 0
SELECT COUNT(*) AS xtp_threads   FROM sys.dm_exec_requests
WHERE command = 'XTP_OFFLINE_CKPT' AND database_id = DB_ID('AppClean');             -- 0

The cutover is a short window where you swap names:

ALTER DATABASE AppDirty SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AppDirty MODIFY NAME = AppDirty_old;
ALTER DATABASE AppClean MODIFY NAME = AppDirty;     -- the clean DB is now live

After the swap, the live AppDirty reports 0 FX filegroups; the leftover AppDirty_old still has its one, and you drop it once you’re happy. This is the straightforward fix when you can afford a maintenance window. The catch — addressed in Scenario D — is scale: for a multi-terabyte database you can’t copy everything inside one window.

Scenario B — the SSDT / DACPAC route (with one trap)

If you deploy from an SSDT project or a DACPAC, you can let your pipeline do the rebuild — but there’s a trap I almost wrote up wrong. Extract a DACPAC from the dirty database:

sqlpackage /Action:Extract /SourceConnectionString:"...;Database=AppDirty" \
           /TargetFile:AppDirty.dacpac /p:ExtractAllTableData=false

You might hope the extract leaves the empty filegroup behind. It doesn’t. Look inside model.xml and the filegroup is right there:

<Element Type="SqlFilegroup" Name="[MemOptFG]">
  <Property Name="ContainsMemoryOptimizedData" Value="True" />
</Element>

Publish that DACPAC straight into a fresh database and it dutifully recreates the filegroup — fx_filegroups = 1, exactly what you’re trying to escape. And no, you can’t wave it away with a publish option: /p:ExcludeObjectTypes=Filegroups still creates it on a fresh-database deploy (I tested it — Creating Filegroup [MemOptFG]...).

The fix is to get the filegroup out of the model. A DACPAC is just a zip of model.xml + an Origin.xml that holds a SHA-256 checksum of the model, so you remove the SqlFilegroup element and rewrite the checksum (a ~20-line script — scrub_dacpac.py in the harness). Publish the scrubbed DACPAC and every object lands on PRIMARY:

SELECT COUNT(*) AS fx_filegroups FROM AppClean_ssdt.sys.filegroups WHERE type = 'FX';  -- 0

The cleaner real-project version: keep the orphaned filegroup out of source control in the first place. It was almost certainly added ad-hoc in production, not committed to your .sqlproj. If your project source never had it, a normal build already produces a clean DACPAC and there’s nothing to scrub — your pipeline deploys to a new database and the filegroup is simply absent. Either way it’s schema-only, so you still load data and cut over with Scenario A’s swap.

Scenario C — no, Always On won’t save you

Here’s the idea everyone reaches for to avoid downtime: stand up a second copy with Always On availability groups (or log shipping, or mirroring) and just fail over. It does not work, and it’s worth understanding why before you spend a weekend on it.

All of those technologies seed the secondary the same way: by restoring a backup of the primary. And a backup of this database carries the memory-optimized container as a file inside it. RESTORE FILELISTONLY shows it plainly:

LogicalName        Type
AppDirty           D
AppDirty_log       L
MemOptContainer    S      <-- the memory-optimized container, in the backup

Restore that backup as a new database — exactly what an AG/log-shipping seed does under the hood — and the copy still has the filegroup:

SELECT COUNT(*) AS restored_fx_filegroups
FROM AppRestored.sys.filegroups WHERE type = 'FX';     -- 1, not 0

Backup/restore moves the whole database, filegroup and all. So any HA technology built on restore inherits the problem. You cannot fail your way out of the filegroup — which means the minimal-downtime path has to move data, not files.

Scenario D — minimal downtime by moving data, not files

For a large, busy database you need the cutover to be seconds, and Scenario C just ruled out the easy seeding trick. The answer is logical data movement: fill the clean database (which never had the filegroup) while the source stays online, then sync only the changes, then cut over.

In production this is transactional replication or a managed migration service. To show the mechanic in the harness I used built-in Change Tracking — no SQL Agent required:

ALTER DATABASE AppDirty SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS);
ALTER TABLE dbo.Customer ENABLE CHANGE_TRACKING;
ALTER TABLE dbo.Orders   ENABLE CHANGE_TRACKING;
DECLARE @v0 BIGINT = CHANGE_TRACKING_CURRENT_VERSION();   -- baseline

1. Seed the clean database in bulk while the source keeps serving traffic.
2. The source keeps changing — inserts, updates, deletes arrive after @v0.
3. Apply the delta: MERGE only the rows that changed since the baseline. The one subtlety that bites people is deletes — a deleted row is gone from the base table, so you must take the primary key from CHANGETABLE, not from the (missing) row:

;WITH ch AS (
    SELECT ct.OrderId, ct.SYS_CHANGE_OPERATION AS op
    FROM CHANGETABLE(CHANGES dbo.Orders, @v0) AS ct      -- PK survives here
)
MERGE AppCleanLogical.dbo.Orders AS tgt
USING (SELECT ch.op, ch.OrderId, o.CustomerId, o.Amount, o.OrderDate
       FROM ch LEFT JOIN dbo.Orders o ON o.OrderId = ch.OrderId) AS src
   ON tgt.OrderId = src.OrderId
WHEN MATCHED AND src.op = 'D' THEN DELETE
WHEN MATCHED AND src.op IN ('I','U') THEN UPDATE SET ...
WHEN NOT MATCHED BY TARGET AND src.op IN ('I','U') THEN INSERT (...) VALUES (...);

After the delta, source and clean match exactly — same row counts, same CHECKSUM_AGG — with the source never having gone offline:

SELECT 'source' AS db, COUNT(*) FROM AppDirty.dbo.Customer ...        -- 5001 / 40000
SELECT 'clean',         COUNT(*) FROM AppCleanLogical.dbo.Customer .. -- 5001 / 40000
-- cust/ord checksums identical; clean FX filegroups: 0

The real cutover is then one more tiny delta inside a brief lock window, followed by the rename swap from Scenario A. Downtime equals the last delta, not a full-size copy.

What I’d actually do

• Under SSDT? Scenario B — just remember the extract carries the filegroup, so scrub it from the model (or keep it out of source). Then deploy to a new database, load data, swap. Lowest effort once you know the trap.
• Small/medium and a window is fine? Scenario A. A scripted rebuild and a rename swap, done in an evening.
• Large/busy, downtime-sensitive? Scenario D. Seed logically, sync deltas, cut over in seconds. Do not reach for Always On/log shipping to seed it (Scenario C) — the filegroup rides along in the restore.

And the meta-lesson from the first post stands: CONTAINS MEMORY_OPTIMIZED_DATA is a one-way door for the life of the database. Once it’s there, removing it is a migration project, not an ALTER — but as these four scenarios show, it’s a very doable one.

Reproduced on SQL Server 2019 Developer in Docker. The harness rebuilds the orphaned state fresh before each scenario, runs all four end to end, and verifies each clean database is XTP-free (0 FX filegroups, 0 XTP_OFFLINE_CKPT threads) — so you can see every receipt yourself.