Generic Solution:
Check the settings for both min server memory (MB) and max server memory (MB). If max server memory (MB) is a value close to the value of min server memory (MB), then increase the max server memory (MB) value.
Check the size of the virtual memory paging file. If possible, increase the size of the file.
For SQL Server 2005:
Install following HotFix and Restart Server.
Additionally following DBCC Commands can be ran to free memory:
- DBCC FREESYSTEMCACHE
- DBCC FREESESSIONCACHE
- DBCC FREEPROCCACHE
Reference : Pinal Dave (https://blog.sqlauthority.com)
24 Comments. Leave new
This is how I resolved it.
I have a 32 bit SQL 2005 SP3 installed on Windows Server 2003 Enterprise SP2. I have been getting an error that stated:
There is insufficient system memory to run this query.
Error: 701, Severity: 17, State: 193.
This article helped me understand the structure of the buffer pool when AWE is enabled.
https://techcommunity.microsoft.com/t5/SQL-Server-Support/bg-p/SQLServerSupport
The basis of the problem was a memory allocation issue. There are basically three memory areas that SQL Server establishes at start up for a 32 bit system with AWE enabled. They are
VAS Reserved User memory
Non-reserved User memory
AWE memory
For any 32 bit application on a server that does not have the /3Gb switch on in the boot.ini file, the OS grants 2GB of User memory. This memory,which is directly addressable to the SQL Server, consists of the VAS Reserved and non-reserved memory. All memory outside of this range is addressable through additional pointers to AWE memory and can only be used for the buffer pool. The problem that my server had was allocating additional pages for the buffer pool. At first I was puzzled because the server has 64GB of memory and I set the max server memory to 56GB, but when I examined it closer I found that the buffer pool works with AWE memory like the OS uses virtual page files.
32 Bit SQL Servers that are 2005 and above have a visible buffer pool that is within the non-reserved user memory and will swap the data contained in the AWE memory as needed for processing. You can view the size of the visible buffer pool using DBCC memorystatus. The server was having problems because the visible buffer pool was too small for the server workload at the time that the memory error occurred.
If you review the CSS article above, you will find the calculations of how much memory is required to map pointers to the pages in AWE memory. It requires 8MB for each Gb of memory on the server. By default, SQLserver will map all of the physical memory on the server at startup. So my server was establishing 512MB of pointers within the 2GB of user memory, even though I had the max server memory set to 56GB.
The resolution was to use the -T836 startup flag and set the max server memory to 48GB. This told the server to only establish pointers to 48GB of AWE memory,hold them in the user process space and only use 384MB for pointers, leaving more memory for the size of the visible buffer pool. Be sure to test this startup trace flag prior to implementing it in production.
My new challenge is to locate objects in the non-reserved memory where I can either reduce the size or eliminate so I can increase the max server memory again.
Hi Pinal i am one of the regular follower of you, i have one doubt could you please let me know the exact resolution.Error as follows “” DBD :: ODBC :: st execute failed :[microsoft] [odbc sqlserver driver][sqlserver] there is insufficient system memory in resource pool ‘default’ to run this query.(SQL-42000)(DBD :st_execute/sqlexecute err =-1) at err_function_lib.pl line 765 “
Hi Pinal. I am getting the same issue
There is insufficient system memory in resource pool ‘default’ to run this query.
Error: 701 Severity: 17 State: 65.
using windows 2003 32 bit ,sqlserver 2008 r2
Error message when you try to run several queries on large data sets at the same time in SQL Server 2008 R2: “701 There is insufficient system memory to run this query”
Is there any fix available for this error?