Friday, April 4, 2014

There is insufficient system memory in resource pool 'default' to run this query

We have encounter “insufficient memory” issue on SQLServer cluster and found below error in Event Viewer and Error.log

2014-04-04 05:36:16.83 spid136 Error: 701, Severity: 17, State: 123.
2014-04-04 05:36:16.83 spid136 There is insufficient system memory in resource pool 'default' to run this query. 


Below are the main reasons for the issue

1. The physical memory is completely used and not available for SQLServer
2. SQLServer engine Max memory allocation has been reached limit.
3. Virtual memory is full

First find out which processes are consuming memory, if any tools or application processes outside of sqlserver consuming and then you can close or kill the process, if not important.

Run below command to find out memory Status

DBCC MEMORYSTATUS

You can also run below commands to clear the memory

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Possible workaround/solution:

If the physical memory on the server is full and you have allocated “Maximum Server memory” for SQL Server close the physical memory of the server then I would recommend decreasing the “Maximum Server memory” of SQL Server to left some memory for Operating system.

If your SQLServer engine max server memory is reached the limit and you still have physical memory available then I would advise increasing the “Maximum Server memory” will help.

If your Virtual memory is full then you need more Physical memory and I would advise to add the same.

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com