Error 701: Insufficient System Memory on a Memory-Capped SQL Server Edition

Fix it now

Error 701 is the engine reporting insufficient system memory in a named resource pool; 802 is the buffer pool itself having nothing left. On Standard and Enterprise that usually points at tuning or at max server memory. On Express it usually points at the edition, whose buffer pool is capped at 1,410 MB however much RAM the machine has.

SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductVersion') AS Version;
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)';
SELECT physical_memory_in_use_kb/1024 AS UsedMB FROM sys.dm_os_process_memory;
SELECT TOP 10 type, SUM(pages_kb)/1024 AS MB FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY MB DESC;

1. Read the edition first. It decides whether the rest of this is tuning or a ceiling you cannot move.
2. Compare what the process is using against the ceiling for your edition and release. Flat at the ceiling with free RAM on the machine is the edition talking.
3. Read the clerk list. If MEMORYCLERK_SQLBUFFERPOOL dominates, this is ordinary pressure; if CACHESTORE_SQLCP does, the plan cache is full of single-use ad hoc plans.
4. On Standard or Enterprise, set max server memory to leave Windows real headroom, then work on whatever the top clerk pointed at.

max server memory (MB) is an advanced option. Calling sp_configure for it without setting show advanced options to 1 first returns error 15123, which reads like a typo and is not one.

If the errors stop under the same workload, you are done. If the numbers say the edition is the ceiling, the next section explains what that ceiling is.

Why it happens

SQL Server does not ask Windows for memory query by query. It takes a large region and manages it internally through memory clerks, of which the buffer pool holding data and index pages is by far the largest. When a statement needs a workspace grant, a plan, a lock structure or a sort area and the pools cannot satisfy it, the engine raises 701 – and note that its published text names a resource pool, so the message tells you which pool ran out even when only the default pools exist. Error 802 is the narrower statement that the buffer pool has no free pages.

Two separate ceilings apply and they behave very differently. The one you set is max server memory (MB). The one the edition sets, you cannot. Microsoft publishes those as scale limits: Express is 1,410 MB of buffer pool per instance whatever the machine holds; Standard is 128 GB on SQL Server 2022 and earlier and 256 GB on SQL Server 2025; Web, which exists up to SQL Server 2022 and is absent from the SQL Server 2025 editions table, is 64 GB. Enterprise is bounded only by the operating system.

There are separate, smaller allowances on top of that figure for columnstore segment cache and memory-optimised data: 32 GB each on Standard, 352 MB each on Express. Those are the numbers to check when a columnstore or In-Memory OLTP workload is the thing complaining, because they are far tighter than the buffer pool ceiling and are easy to miss.

That distinction decides your approach entirely. On Express, a machine with sixty-four gigabytes of RAM still gives the engine 1,410 MB, so adding RAM changes nothing and the money is wasted. On Standard and Enterprise the same errors usually mean something else: max server memory set so high that Windows starves, a plan cache bloated by ad hoc statements, an oversized grant from a bad estimate, or a machine that is simply too small for the work.

Full reference

The documented ceilings, by edition and release

Web edition does not appear in the SQL Server 2025 editions table at all. If you are planning a move and your current instance is Web, the comparison to make is against Standard rather than against a newer Web.

Matching the symptom to where to look

The memory queries worth keeping

SELECT total_physical_memory_kb/1024 AS PhysMB,
       available_physical_memory_kb/1024 AS FreeMB
FROM sys.dm_os_sys_memory;

SELECT physical_memory_in_use_kb/1024 AS UsedMB,
       large_page_allocations_kb/1024 AS LargePageMB,
       memory_utilization_percentage
FROM sys.dm_os_process_memory;

SELECT TOP 10 type, SUM(pages_kb)/1024 AS MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY MB DESC;

Clerk names worth recognising

Setting max server memory without guessing

1. Establish what else runs on the machine: other instances, Reporting Services, an antivirus engine, a backup agent, the application itself.
2. Leave the operating system a genuine allowance rather than a token one, and remember that on a virtual machine the host may be over-committed as well.
3. Set the value explicitly with sp_configure, having enabled show advanced options first.
4. Watch available_physical_memory_kb across a full business day, then adjust once.
5. On Express, set it below 1,410 MB if the machine also runs an application. You cannot raise it, but lowering it stops the two fighting over the same shortage.

Where resource governor now fits

Resource governor lets you put a workload into its own pool with its own memory allowance, which is the clean way to stop one report starving everything else. Microsoft documents it as available in Enterprise, Enterprise Developer, Standard and Standard Developer starting with SQL Server 2025; before that it was Enterprise and Developer only. That is a genuine change in the advice: on a current Standard instance, capping a badly behaved workload no longer requires Enterprise.

Every code this article covers

Confirm the fix worked

1. Errors 701 and 802 stop appearing in the instance error log under the same workload.
2. sys.dm_os_process_memory shows the engine using memory up to your intended ceiling, with Windows still reporting free memory.
3. The query that previously failed completes, and its plan shows a memory grant in proportion to the rows it returns.
4. No single clerk other than the buffer pool dominates sys.dm_os_memory_clerks.
5. If you changed edition, SERVERPROPERTY('Edition') reflects it and the instance now uses more than the old ceiling.

Questions people ask about this