none
There is insufficient system memory in resource pool 'internal' to run this query

    Question

  • I'm running SQL Server 2008 on Windows Server 2003 Standard with 4GB of Memory. I'm getting the following error after the system has been running for about 30 hours. There is insufficient system memory in resource pool 'internal' to run this query. This started back in October and took us two weeks to stop. The system used to have SQL Server 2008 and 2005 running on the same box. To stop the issue in October we removed SQL Server 2005 and the issue went away. After a planned power outage on 11/30 the issue return on 12/1 and has been happening ever since. I have add justed the memory allocated to SQL to 3072 and 2661 but it stills keep happening. I have also stopped all backups with no good results.

    

    

    Friday, December 13, 2013 2:38 PM

All replies

  • are you using full text query..

    Refer this link:

    http://support.microsoft.com/kb/982854/en-us


    Hope it Helps!!

    Friday, December 13, 2013 3:04 PM
  • did you check your memory usage?

    3GB isn't really much...


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Friday, December 13, 2013 3:09 PM
  • Stan,

    I don't believe so and we already have the latest SP3 update installed. I had been runnning fine for over 600 days without any issues.

    Friday, December 13, 2013 3:12 PM
  • Andreas,

    I did and everything looks fine.

    Friday, December 13, 2013 3:12 PM
  • I'm running SQL Server 2008 on Windows Server 2003 Standard with 4GB of Memory. I'm getting the following error after the system has been running for about 30 hours. There is insufficient system memory in resource pool 'internal' to run this query. This started back in October and took us two weeks to stop. The system used to have SQL Server 2008 and 2005 running on the same box. To stop the issue in October we removed SQL Server 2005 and the issue went away. After a planned power outage on 11/30 the issue return on 12/1 and has been happening ever since. I have add justed the memory allocated to SQL to 3072 and 2661 but it stills keep happening. I have also stopped all backups with no good results.

    

    

    Hello,

    Is your system 32 bit or 64 bit?

    Have you set max server memory for SQL server in Sp_configure?

    Does SQl server service account has Locked pages in memory privilege?

    Generally after Out of memory error there is DBCC MEMORYSTATUS output in errorlog file can you run sp_readerrorlog and see if it is present if not please run below query

    DBCC MEMORYSTATUS and paste output here or on Skydrive.

    Can you also paste output of below query this will work from sql 2008 onwards

    select * from sys.dm_os_process_memory

    Please post output of all queries I have asked so that I can help you


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, December 13, 2013 4:21 PM
  • Shanky_621,

    Here is the link the output files. I am running 32 bit. The sql server is runnning from the local services account. When i check the locked pages it says that System has access.

    https://skydrive.live.com/redir?resid=86762B288CBC87AB!1291&authkey=!AOPGYWLEXaHdwhE&ithint=folder%2c

    Friday, December 13, 2013 6:14 PM
  • CACHESTORE_SQLCP is high.

    Can you check the setting of the configuration option "optimize for ad hoc workloads". If it's 0 set it to 1. No need to restart, but you could flush the plan cache with DBCC FREEPROCCACHE.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 13, 2013 11:26 PM
  • Hello Eric,

    Output for query you posted was little haphazard. Your SQL server is 32 bit but i cannot find VAs pressure and I agree with Erland that SQL CP ( cached plans) are using 800 MB which as compared to total RAM is big.It can point to fact that SQL is getting lot of Adhoc queries for which it has to create new plans every time so it is utilizing more memory for cache plans.

    You can also consider optimizing your query .If you are running large batch break it into small batches and run it.Since your SQL server is 32 bit ( hope I am correct) and that too RAM as 4 G you will face some limitations

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Tuesday, December 17, 2013 9:44 AM
    Saturday, December 14, 2013 2:24 PM
  • Erland,

    I tried you suggestion but the issue stilled returned after 30 hours. I have uploaded the results from dbcc memorystatus and uploaded the current errorlog to the link below.

    https://skydrive.live.com/redir?resid=86762B288CBC87AB!1296&authkey=!AEhknPljLe5ZaSc&ithint=folder%2c

    Saturday, December 14, 2013 5:22 PM
  • We're fumbling in the dark here. What does this query report:

    select count(*) FROM sys.dm_exec_xml_handles(0)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 14, 2013 7:04 PM
  • Erland,

    The query returned 0.

    Saturday, December 14, 2013 7:14 PM
  • Good. Then we can rule out the possibility of leaking XML handles.

    I notice that there some consumption of memory for the CLR. Do you know if you have any unsafe assemblies on the machine? Any extented stored procedures? Any use of sp_OAxxx?

    As already noted in the thread, 4GB is a very small amount of memory for a database server these days. However, the effect of only have too little memory in the machine is usually not this kind of hard errors, but only bad performance. So I am not sure that adding more memory would help. (But buying new 64-bit hardware and run 64-bit SQL Server on it, can certainly help.)

    Before I come with more suggestions, can you post the output from

    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    EXEC sp_configure


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 14, 2013 8:07 PM
  • I'm not sure about the assemblies, stored procedures, or sp_OAxxx. The system was already setup before i started. I did do a hardware diagnostic and it is show that 3 out of the 8 hard drives on my server have a high read/write error. It says they need to be replaced. I'm virtualizing the system Monday onto brand new servers.

    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

    name                                minimum     maximum     config_value run_value

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

    access check cache bucket count     0           16384       0            0

    access check cache quota            0           2147483647  0            0

    Ad Hoc Distributed Queries          0           1           0            0

    affinity I/O mask                   -2147483648 2147483647  0            0

    affinity mask                       -2147483648 2147483647  0            0

    Agent XPs                           0           1           1            1

    allow updates                       0           1           0            0

    awe enabled                         0           1           0            0

    blocked process threshold (s)       0           86400       0            0

    c2 audit mode                       0           1           0            0

    clr enabled                         0           1           0            0

    cost threshold for parallelism      0           32767       5            5

    cross db ownership chaining         0           1           0            0

    cursor threshold                    -1          2147483647  -1           -1

    Database Mail XPs                   0           1           0            0

    default full-text language          0           2147483647  1033         1033

    default language                    0           9999        0            0

    default trace enabled               0           1           1            1

    disallow results from triggers      0           1           0            0

    filestream access level             0           2           0            0

    fill factor (%)                     0           100         0            0

    ft crawl bandwidth (max)            0           32767       100          100

    ft crawl bandwidth (min)            0           32767       0            0

    ft notify bandwidth (max)           0           32767       100          100

    ft notify bandwidth (min)           0           32767       0            0

    index create memory (KB)            704         2147483647  0            0

    in-doubt xact resolution            0           2           0            0

    lightweight pooling                 0           1           0            0

    locks                               5000        2147483647  0            0

    max degree of parallelism           0           64          0            0

    max full-text crawl range           0           256         4            4

    max server memory (MB)              16          2147483647  3072         3072

    max text repl size (B)              -1          2147483647  65536        65536

    max worker threads                  128         32767       0            0

    media retention                     0           365         0            0

    min memory per query (KB)           512         2147483647  1024         1024

    min server memory (MB)              0           2147483647  0            0

    nested triggers                     0           1           1            1

    network packet size (B)             512         32767       4096         4096

    Ole Automation Procedures           0           1           0            0

    open objects                        0           2147483647  0            0

    optimize for ad hoc workloads       0           1           1            1

    PH timeout (s)                      1           3600        60           60

    precompute rank                     0           1           0            0

    priority boost                      0           1           0            0

    query governor cost limit           0           2147483647  0            0

    query wait (s)                      -1          2147483647  -1           -1

    recovery interval (min)             0           32767       0            0

    remote access                       0           1           1            1

    remote admin connections            0           1           0            0

    remote login timeout (s)            0           2147483647  20           20

    remote proc trans                   0           1           0            0

    remote query timeout (s)            0           2147483647  600          600

    Replication XPs                     0           1           0            0

    scan for startup procs              0           1           1            1

    server trigger recursion            0           1           1            1

    set working set size                0           1           0            0

    show advanced options               0           1           1            1

    SMO and DMO XPs                     0           1           1            1

    SQL Mail XPs                        0           1           0            0

    transform noise words               0           1           0            0

    two digit year cutoff               1753        9999        2049         2049

    user connections                    0           32767       0            0

    user options                        0           32767       40           40

    xp_cmdshell                         0           1           0            0

    Saturday, December 14, 2013 8:23 PM
  • We are also moving to windows server 2008 r2, but I don't have a date for that yet.
    Saturday, December 14, 2013 8:24 PM
  • The CLR is disabled and so are Ole Automation procedures, so you don't seem to be using them. This query should determine if you have any extended stored procedures:

    select count(*) from master.sys.extended_procedures

    (As for memory still being used by the CLR: the configuration option only concerns user-supplied CLR code. You might still use built-in CLR features, such as the spatial data types.)

    The disk errors are certainly alarming. I don't know if the disk errors could be cause to the out-of-memory errors. It certainly sounds a little farfetched. On the other hand, the disk errors are more urgent to address, as you could face corruption. You should certainly run DBCC CHECKDB on the databases if time permits. And why would so serious appear simultaneously if they are not connected?

    So migrating to new servers sounds like a good idea. If the memory problem persists, we need to take a second look.

    Interesting observation: "user options" is set to 40. This means that ANSI_WARNINGS and ANSI_NULLS are on by default also for clients that run legacy APIs like DB-Library. This has nothing to do with your memory issue, but since you have inherited the system I wanted to make you aware of this setting. (Although one can hope that the applications that required those settings have been decommissioned or rewritten since then.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 14, 2013 9:14 PM
  • Erland,

    The query return 18.

    Monday, December 16, 2013 3:51 PM
  • Hm, on my servers returns 0. (Which puzzles me, because I expected to see the MS-shipped XPs there.)

    Can you lists the names of the XPs, so we can see if you have any local suspects?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 16, 2013 11:15 PM
  • Erland,

    Just to let you know. I set sql to run with with admin account and enable locked pages memory. This seems to have fixed my problem.

    Thanks,

    Eric

    Sunday, December 22, 2013 6:10 PM
  • Great to hear that things worked out! Please come back if the problem reappears.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 22, 2013 6:30 PM
  • Shanky_621,

    Here is the link the output files. I am running 32 bit. The sql server is runnning from the local services account. When i check the locked pages it says that System has access.

    https://skydrive.live.com/redir?resid=86762B288CBC87AB!1291&authkey=!AOPGYWLEXaHdwhE&ithint=folder%2c

    Hello,

    good to know your issue resolved.But here above you said your account alreadt had LPIM privileges.So i did not emphasized on it much.

    >>Just to let you know. I set sql to run with with admin account and enable locked pages memory. This seems to have fixed my problem.

    Did you used another Admin account with LPIM privileges ? If so thanks for confirming I am always a supporter that in almost all cases Account running SQL server must have LPIM privileges(there are some exceptions).But make sure you had set proper MAX server memory before giving LPIM privileges.

    I would like you to keep this thread active if you face any issue please post again


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, December 23, 2013 5:11 AM