SQL Server Performance

There is insufficient system memory to run this qu

Discussion in 'Performance Tuning for DBAs' started by cbabino, Jun 10, 2005.

  1. cbabino New Member

    Every few days we get the error message "There is insufficient system memory to run this query. " When running the transaction log backup. The only way to fix it is to reboot.

    But this does not make sense. It has 8 gibs of ram with awe enabled. Win2003 service pack 1 and 4 xeon processors.

    Any advice would be appreciated.

    thanks
  2. Luis Martin Moderator

    How about to optimize query?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. cbabino New Member

    How do you optimize the transaction log backup?
  4. Luis Martin Moderator

    My mistake.
    I thought in some query same time with transaction log backup.
    How often do you run transaction log backup?
    Also, can you post all message error?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. cbabino New Member

    We run the transaction log backups every 15 minutes. Actually u know what, this may have started happening after we changed the transaction log backups from 1 hour to 15 minutes. Could that be causing it, I wonder. You may have hit on something. Should we change it back to one hour and see what happens.

    Thanks
  6. Luis Martin Moderator

    May be.
    Shot in the dark (because I find nothing in Microsoft) if from one backup to other you are sure that first backup finish, I'll will back to one hour or 30'. Just a thought.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  7. cbabino New Member

    I have changed the transaction log backups back to 1 hour and will monitor it the next few days.
    I will let you know.
    I appreciate your help.
    Thanks
  8. Luis Martin Moderator

    Please, all will appreciate your feedback.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  9. gurucb New Member

    What version of SQl Server are you using?
    It is indicated that AWE is enable, that reminds that SQl Server does not have that setting. So, it must be 2000.

    Changing the transaction log backup from 15 mins to 1 hour might not be the root cause of the problem. Reason when doing it 15 minutes sql server has a lot less work to do than when done in 1 hour.


  10. cbabino New Member

    I just checked today and it is still happening, even though the tran log is backing up every hout instead of 15 mins.


    I don't know what else to try. We may have to contact microsoft support on this one.
  11. satya Moderator

    Have you observed any other errors on SQL SErver error log such as exception errors?
    Confirm SQL Memory settings as well.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. gurucb New Member

    It is a memory related error

    Run select * from sysprocesses when you have the transaction log backups running.

    see the most of the waittypes and wait resources and wait times.

    If you see some thing like Resource_semaphore look for Memory grants pending and Memory grants outstanding.

    If you find these values consistently more then there are some queries that are doing large hash joins.

    do a update statistics with full scan and
    dbcc dropcleanbuffers and dbcc freeproccache to force all the execution plans to be regenrated.


  13. cbabino New Member

    The following is what microsoft told us.

    Apparently processing data using a linked server specifially the fox pro driver causes memory leakage which builds up over time.


  14. joechang New Member

    when ever you get an insufficient memory message,
    its talking about virtual memory, the private address space each application process gets,
    not physical memory,
    so when you have a memory leak, it eventually runs out of address space, and a call to virtualalloc fails, reported as insufficient memory,
    regardless of how much physical memory is available
  15. gurucb New Member

    Then if it is the case did you get any other error meesage like:
    Failed to reserve contiguous memory of size in the error log.

    If it is related to linked server, starting sql server with -g parameter or bringing the driver for fox pro out of sql server address space should work.

    What did the PSS guys do?

  16. sunilpathania New Member

    In addition to message 'There is insufficient system memory to run this query', another frequent error message is there in sql server error log :
    "Downgrading backup buffers from 960K to 64K". We are not using any linked server.
  17. satya Moderator

    Welcome to the forum!
    Its good to refer the old thread that is similar to your problem, also it will be better if you can mention about your configuraiton for more information.
    Do you have any resource intensive QUERIES with multiple outer joins that can cause this issue.
    Also what is the service pack leve on SQL Server including the memory settings?
    KBA http://support.microsoft.com/kb/815114 fyi and also you may be using a lot of external SQL Server calls to .dlls: linked server usage, xp extended proc usage, usage of sp_OA etc., calls to OPENXML, the heavy use of LOB objects. All of these can fragment what is known as the MEM TO LEAVE area. The only way to cure it is to reboot the server. To keep it from happening, or as often, reduce your dependence on the databsae server from these external objects..

Share This Page