SQL SERVER – Fix : Error 701 There is insufficient system memory to run this query

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 (http://blog.SQLAuthority.com)

About these ads

24 thoughts on “SQL SERVER – Fix : Error 701 There is insufficient system memory to run this query

  1. Hi!

    We had exectly the same problem, on Windows 2003 32 bit & SQL 2005 SP2 with AWE enabled, when trying to make Database Integrity Check (Database size 60GB, with one hudge, 22 GB Table. Yeah, I know, it’s another problem and we are working on it :) ) there was “Error 701 There is insufficient system memory to run this query” error. After that, server was runngin slow, with strange errors in log, a lot of CLR errors and so on and the only solution was SQL Server restart. Also during working day, there were a lot of locks and sometimes the only solution was restarting the server, because killing locked processes didn’t helped.

    Yestarday I tried to manually configure Max server memory and… IT WORKS! Integrity check works on 60GB database and besides server WORKS MUCH MORE STABLE. It’s strange, but I think that there is some strange memory management error in AWE (or in something related) and it is reflected on complex operations and objects.

    I just wanted to thank you for helping with this terrible problem, SETTING MAX SERVER MEMORY WORKS FOR ME.

    P.S. the better solution is to migrate to x64 :) but it’s difficult on production server…

    Like

  2. Hi, I have the same error; I really worry about it, because some process (importantant process) that run in my applications made the error when they run; it’s really important to solve this issue, I installed sp2 and the problem continue.. I also executed a disk defracmentation and the result no’t change.

    Any other idea to solve the problem…
    I really apreciate any help
    thanks in advance.

    Jagustin

    Like

  3. Hi,

    Im using Windows NT with sql server 6.5.
    now i days i facing a problem when i m going enterprise manager and open the database, sql query, or any other option its shows the error msg.

    “A Connection could not be establish to Fisher(Data base name). [SQL Server] There is insufficient system momory to run this query”.

    i change my system memory update virtual system memory.

    Kindly tell me how i resolve this problem

    Like

  4. Hi!

    We had exactly the same problem, on Windows 2003 32 bit & SQL 2005 SP2 with AWE enabled… Like Gode we configure Max server memory, first day it works but now it crashes again.
    I want to ask that what was your maximum server memory, GODE?

    If anybody help us, I will be gratefull

    Like

  5. Hi team,

    I am getting the same error in my sql server. Please assist me to know where do is specify the min and max server memory.

    I would be grateful to get this resolved.

    Thanks,

    Joseph

    Like

  6. Hello Parag,

    Check the value of “min memory per query” option. Default value of this option is 1024 KB. Even not recommonded but you can try a lesser value for this option. Let us know if that helped.

    Regards,
    Pinal Dave

    Like

  7. The maximum server memory on our server is set to 2147483647 MB and our virtual memory is 2046 MB, would the maximum server memory setting cause Error 701? Do we need to set the max server memory to a realistic number?

    Like

  8. @ Pinal Dave: what are the standard values?
    We have an W2k8 member server; x64 with 8 Gb physical memory; SQL 2008 and now and then (once a day) event ID 701.
    Min memory = 0 max memory = 2147483647 MB
    Min memory per query = 1024 KB

    Like

  9. Hey there. First of all thank you.. I have found your site to be a great resource on a couple of instances now, and I think you have helped me again.

    Here is what I found. I have a 64 bit Windows server running 32 bit SQL 2005 (SP3)

    I think the issue here is that I have 16GB physical memory on the server. My SQL MAX memory is set to 11000 MB, and my MIN is set at 0

    I have a system PageFile that is only 4GB.

    I have SQL running with a service account, and that service account did not have the system policy of “Lock Pages in Memory”

    Seems to me that if SQL is trying to Page out to the pagefile and its all full up , i might have troubles. Makes sense to me.

    Like

  10. Dear All,

    Please help me as I am suffering from the same error. We have a reporting toolthat works on the SQL2008. But it gives the same error. The memory min=0 and the max=2147483647 MB. The Minimum memory per query is 1024.

    Can anyone suggest why this occurs. Once this problem occurs I have to start the reporting toola nd also the sql express service.

    Your reply is appreciated. Thanks in advance..

    Like

  11. Re: Additionally following DBCC Commands can be ran to free memory:

    DBCC FREESYSTEMCACHE
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE

    How will running these fix the problem? If these are run in a production system then this can cause other performance problems. This is extremely bad advice!

    DBA

    Like

  12. Once the insufficeint memory space problem shows up, restore your system to a restore point before it occured and bam you are back in business.

    Like

  13. 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.

    http://blogs.msdn.com/b/psssql/archive/2012/12/11/how-it-works-sql-server-32-bit-pae-awe-on-sql-2005-2008-and-2008-r2-not-using-as-much-ram-as-expected.aspx

    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.

    Like

  14. 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 “

    Like

  15. 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

    Like

  16. 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?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s