About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
Error 701 means SQL Server failed to allocate enough memory to run a query. Microsoft states that "In most cases, the transaction that failed isn't the cause of this error", so tuning that one query is not the first thing to try. The shortage comes from one of three places, and the checks below tell you which.
If the error appears once and clears, Microsoft says there might be "a short-lived memory issue that resolved itself" and you might not need to act. Act when it recurs, hits several connections and lasts seconds or longer.
What the error means
Microsoft lists event 701 under source MSSQLSERVER, symbolic name NOSYSMEM, message text "There's insufficient system memory to run this query." The Learn page groups the causes into three:
- External or OS memory pressure. Something outside the SQL Server process is using memory. Microsoft explains that SQL Server responds to Windows low-memory signals by reducing its own memory, "Very few other applications respond", so SQL Server shrinks while the other software does not, and components then fail to get memory.
- Internal pressure not from SQL Server components. Code that runs inside the SQL Server process but is not part of the engine: linked server providers, SQLCLR, extended procedures, OLE Automation (
sp_OA*), and security or monitoring software that injects DLLs. Microsoft's example is a linked server caching 20 million rows. No memory clerk reports it, yet the process memory is high. - Internal pressure from SQL Server components. One of the hundreds of components tracked as memory clerks holds a large share.
Error 701 is a failed allocation. A query that is waiting for a memory grant is a different symptom, and none of the checks below is about grant queues.
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| Other software uses the host's memory | Available MB low; SQL Server's working set falling | Memory:Available MB and Process:Working Set for other processes |
| Code inside the SQL Server process | Process private bytes much higher than SQL Server's total server memory | Compare the two counters; list loaded modules |
| Large memory clerk | One clerk dominates pages_kb |
Query sys.dm_os_memory_clerks repeatedly |
| Max or min server memory set badly | Cap too high for the host, or too low for the load | Server memory configuration options |
| Memory overcommitted on a virtual machine | Guest sees little free memory while the host is short | Hypervisor memory counters; Microsoft points to guidance on overcommit detection |
| Resource Governor limits | Errors only for one workload group | Resource pool and workload group settings |
| Many concurrent large queries | Errors during peak concurrency | Concurrent sessions and running queries |
Prove the cause before changing anything
All of the following only read. Collect over time, not once, because Microsoft says to collect periodic outputs.
- Performance Monitor counters. Microsoft's list: Memory: Available MB; Process: Working Set; Process: Private Bytes; SQL Server: Memory Manager (all counters); SQL Server: Buffer Manager (all counters). Microsoft supplies a sample using
Get-Counterwith\Memory\Available MBytes,\Process(*)\Working Setand\Process(*)\Private Bytes. - Memory clerks. Run Microsoft's query on a schedule while the errors occur:
SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC
- DBCC MEMORYSTATUS. Microsoft names it as a more detailed alternative: "you can observe the more detailed DBCC MEMORYSTATUS output and the way it changes when you see these error messages."
- System and Application event logs. Microsoft supplies
Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"and the same for Application. - In-process modules. Microsoft gives
tasklist /M /FI "IMAGENAME eq sqlservr.exe"andSELECT * FROM sys.dm_os_loaded_modules. Look for modules you did not expect. - The private-bytes comparison. Without Lock Pages in Memory, most SQL Server memory shows in Process: Private Bytes for the sqlservr instance. If Private Bytes is significantly above SQL Server: Memory Manager: Total Server Memory (KB), Microsoft says the difference is likely from a DLL such as a linked server, extended procedure or SQLCLR. Microsoft states that with Lock Pages in Memory this comparison is harder and approximate.
For an automated capture, Microsoft names PSSDIAG (the Perfmon collector and the SQL Memory Error collector) and SQL LogScout (the Memory scenario).
Take each sample with a timestamp and keep the files. One sample taken after the error has passed shows a healthy server, and the comparison between samples is what shows the shortage building. Compare the clerk listing taken just before an error with one taken in a quiet period; the clerk that grows between them is the one to investigate. If no clerk is large and the private bytes figure is also close to total server memory, return to the first group and look at the rest of the host.
Read the results in plain terms. If Available MB is low and another process holds the memory, the pressure is external. If private bytes exceed total server memory by a large margin, it is in-process. If one clerk dominates, it is internal to the engine.
Fix
External pressure. Microsoft's guidance: fix code or configuration of less critical applications, stop or reschedule them, or move them to another server.
Disruptive step: stopping or moving another application interrupts its users. Confirm with its owner. To back out, restart it.
In-process code. For a linked server that may be consuming memory, Microsoft says you can run it out of process by disabling the Allow inprocess option, noting that not all OLE DB providers run out of process. For OLE Automation objects, setting context to 4 (Local .exe OLE server only) runs the object outside SQL Server.
Disruptive step: changing a linked server provider or OLE Automation context changes how queries behave and may fail if the provider cannot run out of process. Test first. To back out for a linked server, re-enable the Allow inprocess option on its provider. To back out for OLE Automation, set context back to the value your code used before (the value you changed from), and note that value before you change it.
Memory clerk examples from Microsoft.
- MEMORYCLERK_SQLQERESERVATIONS: find queries with huge memory grants and optimise them with indexes, rewrites such as removing ORDER BY, or query hints.
- CACHESTORE_SQLCP (ad hoc plans): parameterise queries by stored procedures,
sp_executesqlor forced parameterisation. - CACHESTORE_OBJCP: identify which procedures, functions or triggers use the memory; this can mean redesign.
- OBJECTSTORE_LOCK_MANAGER: find queries that take many locks, shorten transactions, or check whether lock escalation is disabled.
Disruptive step: FORCED parameterization is a database-level setting, so it changes how every query in that database is handled. Microsoft's 701 page names it without describing its effects, so test it on a copy first. Note the current setting before you change it, and to back out set it back to that value. Rewriting queries, adding indexes or adding hints also changes behaviour: test each change and keep the previous query text or index definition to restore.
Quick relief. Microsoft lists: check max server memory and min server memory and "consider increasing max server memory if possible"; if max server memory has not been set, especially with Lock Pages in Memory, set it to leave memory for the operating system; check the workload; and on a virtual machine make sure memory is not overcommitted. It also names these caches to clear: DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE and DBCC FREEPROCCACHE.
Disruptive step: the three DBCC FREE commands discard cached data. Microsoft's DBCC FREEPROCCACHE page says that clearing the plan cache evicts all plans and incoming queries compile new ones, which "can cause a sudden, temporary decrease in query performance". There is no way back except letting the caches refill. Use them as a short bridge, not as a fix.
Disruptive step: a max server memory value that is too high can bring back the host-level pressure. Record the old value to put back, and put it back if Available MB falls again.
Resource Governor. If used, Microsoft recommends checking pools and workload groups so they do not limit memory too drastically.
Disruptive step: changing a Resource Governor pool or workload group changes the memory available to that workload for every session in it. Record the old limits first. To back out, restore those values.
Confirm the fix
- Keep the same counters and the clerk query running for a full busy period and check that error 701 no longer appears in the error log.
- Compare
pages_kbfor the offending clerk before and after. - Check that Available MB stays above its earlier low point.
Example (illustrative): sql01 has 128 GB of RAM. During the nightly load, Process: Private Bytes for sqlservr reads 92 GB while SQL Server: Memory Manager: Total Server Memory reads 64 GB. The difference is 92 - 64 = 28 GB, which is 30.4% of the private bytes held outside the engine's accounting. sys.dm_os_loaded_modules shows a third-party provider DLL. The linked server using it is set to run out of process, and the next night's difference falls. Memory clerks showed nothing large, which matches Microsoft's description of an in-process module.
What to watch afterwards
- Keep the counter capture for at least one full cycle of the heaviest job.
- Re-run the clerk query after schema or workload changes.
- Re-check max server memory after changes to the VM's size.
- Treat a rise in a single clerk across days as a trend worth a ticket.
Read next
Related articles you may want next.
- HostingSQL Server memory on a hosted VM: max server memory, lock pages and what to leave for WindowsHow to size SQL Server's memory cap on a hosted virtual machine using Microsoft's own method, when min server memory matters on a VM, and when granting Lock Pages in Memory is and is not appropriate.Read article
- PerformanceSQL Queries Wait for Memory: Diagnose RESOURCE_SEMAPHOREDistinguish excessive grants, underestimated grants and concurrency pressure before changing memory settings.Read article
- TroubleshootingWindows Server Out of Memory With No Busy Process: DiagnosisServer short of memory but no process explains it? Measure pool and page file counters over hours, then check pool tags and handle counts that grow.Read article
Risks and limits
These commands have not been run or lab-tested. Microsoft's page is general; it gives no fixed numbers for how large a difference is a problem, so the 28 GB figure above is illustrative only. Tasklist, Get-Counter and Get-EventLog are Windows commands. On SQL Server on Linux, use the DMVs and Linux tools instead.
Do not restart SQL Server as the first response; it clears the memory state that shows which memory was short. Do not raise max server memory on a host that is already short, and do not clear caches repeatedly to keep errors away.
Sources
- MSSQLSERVER_701 — Microsoft Learn, page dated 2026-05-20, accessed 18 November 2026
- DBCC FREEPROCCACHE (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
This article was originally published on .