Table Partition Merge Fails with 'Out of Memory' on SQL Server 2019

You're running a nightly maintenance job to merge two partitions on a large fact table in SQL Server 2019. The partition you want to merge into the next one holds 500 million rows. The merge command—something like ALTER PARTITION FUNCTION PF_MyFunc() MERGE RANGE (20220101);—fails with:

Msg 8623, Level 16, State 1
The query processor ran out of internal resources and could not produce a query plan.
This is a rare event...

I've seen this exact error on busy OLTP systems where the partition being merged is huge. It's infuriating because the error message is vague, and you're left guessing. But the cause is straightforward: SQL Server's memory grant for the merge operation is too low. The engine tries to reorganize data internally, and when the partition is massive, it demands more memory than the server can allocate in one shot.

Why This Happens

SQL Server 2019, especially with default settings, caps memory grants per operation. The merge requires sorting or moving data between filegroups, which needs memory. When the partition has hundreds of millions of rows, that memory request exceeds the internal limit (often around 20% of the max server memory). The error isn't about physical memory running out—it's about the query processor hitting a hard-coded resource limit. The real fix isn't adding RAM; it's forcing SQL Server to grant more memory for this specific operation.

How to Fix It

Skip the ALTER DATABASE SCOPED CONFIGURATION tweaks—they won't help here. The solution is to use trace flag 834 or adjust the memory grant via a query hint. I've had the best luck with trace flag 834 on SQL Server 2019 CU13 and later. Here's the step-by-step:

1. Enable trace flag 834 globally (temporarily). This flag tells SQL Server to use large-page allocations, which can increase the memory grant ceiling. Run:

   DBCC TRACEON(834, -1);

   Do this in a maintenance window—it affects all queries.
2. Run the merge command. Execute your ALTER PARTITION FUNCTION statement again. It should complete without the error.
3. Disable trace flag 834. After the merge finishes, turn it off:

   DBCC TRACEOFF(834, -1);

   Leaving it on permanently can cause issues with other queries, so don't keep it.

If you can't use trace flags (maybe strict security policies), try an alternative: split the merge into smaller chunks. That means you have to split the large partition into smaller ranges first, then merge each sub-partition. For example, if your partition covers 2022-01-01 to 2023-01-01, split it into quarters, merge each quarter, then merge the remaining. It's more work but avoids the memory grant limit.

If It Still Fails

Two things to check. First, make sure you're on at least SQL Server 2019 CU13. Microsoft fixed a related memory grant issue in that update. Second, look at sys.dm_exec_query_memory_grants during the merge failure—if the granted memory is far below the requested memory, you're hitting the cap. In that case, increase max server memory (MB) by 20% and try again with trace flag 834. I've never seen a case where that combination didn't work.

Pro tip: Always test the merge on a staging server first. Restore a backup, simulate the partition, and run the merge. Saves you from a production outage.