"There is insufficient system memory in resource pool 'internal' to run this query" error message

  • I am getting this error on one of the production servers. All the jobs are failing because of this error. Version of sql server is 2008 sp3.

    I have checked msdn and it's a bug which was fixed in 2008 sp2 edition. But I am not sure why I am receiving this error on SP3.

    Thanks,

  • deep_kkumar (6/6/2012)


    I am getting this error on one of the production servers. All the jobs are failing because of this error. Version of sql server is 2008 sp3.

    I have checked msdn and it's a bug which was fixed in 2008 sp2 edition. But I am not sure why I am receiving this error on SP3.

    Thanks,

    ITs only a bug if it is showing when it is not supposed to. Sorry, since you did not put the error itself into the actual post, I can't see it anymore, it is cut off.

    EDIT: Ok, I saw it. How much memory do you have on the machine, what is the max SQL setting, and do you have any processes running that are using memory? Is resource governor enabled?

    Jared
    CE - Microsoft

  • we have 128 Gb of memory on the server.

    max memory allocated for sql server is 100 GB.

    Resource governor not enabled.

    Full text filter daemon launcher is enabled.

  • Hmm... I'm no expert in this, but you may want to look at DBCC MEMORYSTATUS

    Jared
    CE - Microsoft

  • Hi Deep_kkumar,

    we will need to analyze the results of DBCC MEMORYSTATUS before suggesting anything. Please paste the output of DBCC MEMORYSTATUS

    =======================================================================================

    Visit my technical reference; you might find some of your issues already documented.

  • That's a lot of data to paste over here.

    Can you guys suggest in particular which sections you need in particular?

  • deep_kkumar (6/6/2012)


    That's a lot of data to paste over here.

    Can you guys suggest in particular which sections you need in particular?

    Let's start with the first section, I'd like to see the reserved memory in use.

    Jared
    CE - Microsoft

  • What's the query? (btw, this has to do with workspace memory grants, not necessarily the available buffer pool)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Memory Manager KB

    ---------------------------------------- -----------

    VM Reserved 141122460

    VM Committed 687728

    Locked Pages Allocated 63700992

    Reserved Memory 1024

    Reserved Memory In Use 0

  • GilaMonster (6/6/2012)


    What's the query? (btw, this has to do with workspace memory grants, not necessarily the available buffer pool)

    this error blocks all the queries to run. All the jobs failed as there was insufficient memory.

  • Every single query you run fails with that message?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Being that you have 128 GB of memory I am assuming that this is 64-Bit but just to be sure, is it?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David Benoit (6/6/2012)


    Being that you have 128 GB of memory I am assuming that this is 64-Bit but just to be sure, is it?

    I believe it must be, that and the forst section of the results from DBCC MEMORYSTATUS shows locked pages instead of AWE.

    Jared
    CE - Microsoft

  • GilaMonster (6/6/2012)


    Every single query you run fails with that message?

    YES.

  • David Benoit (6/6/2012)


    Being that you have 128 GB of memory I am assuming that this is 64-Bit but just to be sure, is it?

    YES

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply