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

  • Question

  • My Integrity job started failing recently with the following error. Msg 701, Level 17, State 123, Line 1  There is insufficient system memory in resource pool 'default' to run this query.  Process Exit Code 1.  The step failed.

    select @@ version
    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
     Jul  9 2014 16:04:25
     Copyright (c) Microsoft Corporation
     Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    System is having 4GB RAM and SQL is using most of it. It has 2 processors.

    Any help on this will be appreciated .

    Tuesday, July 7, 2015 4:41 PM

Answers

  • In general, this error means "simplify the query or add more memory". It is hard for us to say anything more than that without more info, as requested already in this thread.

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, July 7, 2015 8:09 PM
    Moderator
  • The job Ran successfully last time(May be it got some air to breath at that time). I believe I need to ADD more Memory as its 4GB total and SQL is allowed for 3. During non-peak time itself SQL uses 2.5Gigs and total usage is around 3.8GB which leave only few MB's for OS and cant think when INTEGRITY is running!!!

    As of now we have bounced the server and now its fine. :)  We will be keeping an eye for some days and if it occurs again we will be adding more memory to it.

    But Thanks to ALL for your replies...


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue




    Thursday, July 9, 2015 7:40 PM

All replies

  • What is the database size?

    Have you looked at this forum post?

    https://social.technet.microsoft.com/Forums/sqlserver/en-US/ff936d46-d210-4326-a112-0501a366ea41/dbcc-checkdb-failure-sql-2012-there-is-insufficient-system-memory-in-resource-pool-internal-to

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Tuesday, July 7, 2015 5:09 PM
  • Jinu,

    Unless you post output from DBCC MEMORYSTATUS  and SQL Server when OOM error occurred I cannot tell you what caused this OOM condition

    If you can please post above two outputs on Shared drive like Onedrive, dropbox for analysis and share link here, I can access the logs and can give you few pointers

    I can see you have Virtual server what is total RAM on the system ?

    Any other process apart from SQL Server which is running on OS ?

    What is DB size combined for all database ? How much is tempdb size


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Tuesday, July 7, 2015 6:21 PM
    Moderator
  • In general, this error means "simplify the query or add more memory". It is hard for us to say anything more than that without more info, as requested already in this thread.

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, July 7, 2015 8:09 PM
    Moderator
  • DBCC MEMORYSTATUS:-  https://onedrive.live.com/redir?resid=AC0D80392595C52B!108&authkey=!AECZ0oaqBh1MWcQ&ithint=folder%2cJPG

    SQL Error Log

    Date  7/7/2015 9:47:40 PM
    Log  SQL Server (Current - 7/8/2015 12:00:00 AM)
    Source  spid54
    Message
    Error: 701, Severity: 17, State: 123.

    Date  7/7/2015 9:47:40 PM
    Log  SQL Server (Current - 7/8/2015 12:00:00 AM)
    Source  spid54
    Message
    There is insufficient system memory in resource pool 'default' to run this query.

    TempDB :- 4846.40 MB

    Total SIze UserDB's :- 90Gigs

    RAM size I cant get that right now as its taking a hell lot of time to launch things...

    Wednesday, July 8, 2015 2:30 PM
  • VMCommitted is 2593456KB---2.4G total memory used.  This is total physical memory + virtual used by SQL Server .

    One of your database is 90 G there would be many other databases

    You did not tell us Total RAM on system I assume you have 3-4 G RAM and out of that you have allocated 3 G to SQL Server

    All this leads m to believe that RAM present on your system is very less. Checkdb highly utilises RAM

    Memorystatus seems OK may be because you took output when things were normal. I believe when OOM is encountered memorystatus relevant output is dumped in errorlog that output provides better facts about memory allocation


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Thursday, July 9, 2015 5:06 AM
    Moderator
  • 90 GB is the size of all DB's. This is a small instance and it have only 3 DB's. I understand the resource contention that CHECKDB causes but it had ran successfully many times previously. I took both SQL Server error log and DBCC memory status output while the job was running and failed.


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Thursday, July 9, 2015 11:45 AM
  • The job Ran successfully last time(May be it got some air to breath at that time). I believe I need to ADD more Memory as its 4GB total and SQL is allowed for 3. During non-peak time itself SQL uses 2.5Gigs and total usage is around 3.8GB which leave only few MB's for OS and cant think when INTEGRITY is running!!!

    As of now we have bounced the server and now its fine. :)  We will be keeping an eye for some days and if it occurs again we will be adding more memory to it.

    But Thanks to ALL for your replies...


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue




    Thursday, July 9, 2015 7:40 PM