none
"There is insufficient system memory to run this query" during NTBackup

    Question

  • We have a Dell 2950 III with 32GB RAM and dual quad core Xeons running Server 2003 Standard R2 x64 and SQL Server 2005 Std x64 with SP3 installed.  A few months ago we were seeing these errors in the event log every time we backed the server up with NTBackup, but then they magically stopped happening.  Now I'm seeing them again, just not as much (only on a few instances).  A bit more background:

    Our application requires a separate instance per app server, so we currently have 17 SQL instances (15 active) running on the box.  For our larger instances, we've tried limiting the amount of memory for that SQL instance, but that didn't seem to have much of an impact.  A while ago I found an article that stated Server 2003 x64 didn't handle the file system cache well and that was the issue; I installed a service that was supposed to help manage that, but that didn't seem to help.  I'm now at a loss.  Anybody have any ideas as to why these errors keep cropping up and what we can do to try and resolve them?  Any help would be appreciated! Thanks!
    Friday, June 26, 2009 3:55 PM

All replies

  • Friday, June 26, 2009 4:01 PM
  • Hi, This error usually refers to the first 2 GB the application uses (sorry I am not a specialist of memory subject so feel free to correct me if I say some incorrect details). If you see it from SQL Server logs, don't search on Windows side, the solution is not from there ! This problemn comes from "MemToLeave" on 32 bits SQL Server instances that is too much fragmented or nearly full (it is NOT your SQL memory allocation, and PAE+AWE is NOT a solution : only usefull for Buffer Pool). This area (MemToLeave) is fixed allocated at the startup of the instance and is dedicated for some usages outside Buffer Pool (multi pages allocations, CLR, backup buffers, COM & ActiveX objects, linked servers memory). You have to find out what in your SQL instance eats lot of this memory and try to reduce it. If not possible : - you can increase its size, but use with caution, see "-g" startup parameter - you can add a new option in SQL backup command line (introduced by one of the SP of SQL2000) : with blocksize (you may have seen some "trying to reduce backup buffer block size" or something similar in your errorlog before) - you have to switch to 64bits where this area is not fixed and stored outside SQL memory allocation (in that case, keep an eye of windows free memory) Note that an instance restart, as it frees and realocates memory regions, temporarily stops the problem for an instance. Guillaume N.
    Saturday, November 21, 2009 11:18 AM
  • Hi, This error usually refers to the first 2 GB the application uses (sorry I am not a specialist of memory subject so feel free to correct me if I say some incorrect details). If you see it from SQL Server logs, don't search on Windows side, the solution is not from there ! This problemn comes from "MemToLeave" on 32 bits SQL Server instances that is too much fragmented or nearly full (it is NOT your SQL memory allocation, and PAE+AWE is NOT a solution : only usefull for Buffer Pool). This area (MemToLeave) is fixed allocated at the startup of the instance and is dedicated for some usages outside Buffer Pool (multi pages allocations, CLR, backup buffers, COM & ActiveX objects, linked servers memory). You have to find out what in your SQL instance eats lot of this memory and try to reduce it. If not possible : - you can increase its size, but use with caution, see "-g" startup parameter - you can add a new option in SQL backup command line (introduced by one of the SP of SQL2000) : with blocksize (you may have seen some "trying to reduce backup buffer block size" or something similar in your errorlog before) - you have to switch to 64bits where this area is not fixed and stored outside SQL memory allocation (in that case, keep an eye of windows free memory) Note that an instance restart, as it frees and realocates memory regions, temporarily stops the problem for an instance. Guillaume N.
    He has Windows & SQL 64 bit so Memtoleave shouldn't be the issue here. A really useful resource to read

    http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx


    Are there any other errors/warning in error log ?

    If you have a running 15 instances on same box then there is a chance that a resource "fight" can happen. How do you manage memory for all instances ?  Do you define two Gbytes as Max memory for each instance (you have 32Gb & 15 instances )?

    Just curious , what is the idea behind "Our application requires a separate instance per app server" ?


    As a starting point , Could you please run the following query when the issue happens :

    DBCC MEMORYSTATUS
    GO

    I also suggest monitoring some memory counters using perfmon :

    SQLServer:Memory Manager: Total Server Memory (KB)

    SQLServer:Memory Manager: Target Server Memory (KB)

    SQLServer:Memory Manager:  Memory Grants Pending 

    HTH




    Please mark as answer if you think this answers your questions
    Saturday, November 21, 2009 6:09 PM