Troubleshoot Error 701 - There is insufficient system memory to run this query

Question: How do I troubleshoot a “701 - There is insufficient system memory to run this query” error message?

Answer: According to the SQL Server Books Online “There is insufficient system memory in resource pool '%ls' to run this query.”

If you want to read a problem scenario  check  Error 701 - There is insufficient system memory to run this query    and    SQL Server - Error 701 - There is insufficient system memory

99% of the times I’ve seen this error , the cause is not the current transaction. Although it could be the current transaction which caused the failed memory allocation.

Diagnostic queries usually won’t run when this error message appears.

This troubleshooting list is not exhaustive- but it’s  a priority list I use

1)     Check other services on the server. How much memory are they consuming? It isn’t unusual with external programs such as monitoring agents and AntiVirus programs to consume large amounts of memory

2)     SQL Server memory configurations – Check SQL Server Maximum memory  . Commit some time to workload analysis to calculate an accurate memory requirement for the SQL Server Instance

3)     Troubleshoot memory pressure with Default Trace and Server Memory Change event class

4)     Sys.dm_os_memory_clerks and AWE memory allocation