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

  • Question

  • hello Everyone,

    today i am getting one error while opening activity monitor.

    i am unable to open AM and i checked in sql server error log i received error message description about memory dump.

    below mentioned message. 

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

    Thursday, August 28, 2014 8:51 AM

Answers

  • Can you post more details about this. Message is general messag about OOM. Can you post more information from errorlog. Can you please tell was there any process running when you got OOM error. I have not seen AM giving OOM error.

    How much RAM on windows server ?

    What is output of below

    select @@Version

    and

    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys. dm_os_process_memory


    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 Articles

    • Marked as answer by Baraiya Kirit Wednesday, September 10, 2014 9:44 AM
    Thursday, August 28, 2014 9:09 AM
    Moderator
  • Don't know the build of your SQL Server, but maybe this can help: KB 982854

    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    • Marked as answer by Baraiya Kirit Wednesday, September 10, 2014 9:44 AM
    Thursday, August 28, 2014 9:11 AM

All replies

  • Can you post more details about this. Message is general messag about OOM. Can you post more information from errorlog. Can you please tell was there any process running when you got OOM error. I have not seen AM giving OOM error.

    How much RAM on windows server ?

    What is output of below

    select @@Version

    and

    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys. dm_os_process_memory


    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 Articles

    • Marked as answer by Baraiya Kirit Wednesday, September 10, 2014 9:44 AM
    Thursday, August 28, 2014 9:09 AM
    Moderator
  • Don't know the build of your SQL Server, but maybe this can help: KB 982854

    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    • Marked as answer by Baraiya Kirit Wednesday, September 10, 2014 9:44 AM
    Thursday, August 28, 2014 9:11 AM
  • Try to optimize the query and check what else is utilizing the RAM apart from sql server


    aa

    Thursday, August 28, 2014 9:16 AM
  • You may also face http://support.microsoft.com/kb/982854

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 28, 2014 9:20 AM
    Answerer
  • Hi Baraiya,

    Based on my understanding, you came across an issue when you opening activity monitor. Then you checked SQL Server error log and found error message “There is insufficient memory in resource pool ‘internal’ to run this query… ”.

    Firstly, I would like to know if you have checked event log (Windows Application log). I would appreciate it if you can provide event log and detailed SQL Server error log.

    Besides, I suggest you use DBCC MEMORYSTATUS command to trouble Microsoft SQL Server memory consumption issues. For detail information, please refer to this article: How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005 (http://support.microsoft.com/kb/907877).

    Best regards,
    Qiuyun Yu


    Friday, August 29, 2014 7:27 AM
  • RAM: 16 GB

    Proceesor : 24 cores

    Version:

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

    Error:

    Memory Manager                                 KB ---------------------------------------- ---------- VM Reserved                                 6350064 VM Committed                                 55380 Locked Pages Allocated                            0 Reserved Memory                                1024 Reserved Memory In Use                            0 2014-08-28 08:34:10.54 spid51      Memory node Id = 0                             KB ---------------------------------------- ---------- VM Reserved                                 6348976 VM Committed                                 54352 Locked Pages Allocated                            0 MultiPage Allocator                         22344 SinglePage Allocator                         7800 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLGENERAL (node 0)                 KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                         1224 MultiPage Allocator                            2800 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLBUFFERPOOL (node 0)             KB ---------------------------------------- ---------- VM Reserved                                 6316032 VM Committed                                 22784 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             0 MultiPage Allocator                             400 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLQUERYEXEC (node 0)                KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                            120 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLOPTIMIZER (node 0)                KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                            648 MultiPage Allocator                             976 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLUTILITIES (node 0)                KB ---------------------------------------- ---------- VM Reserved                                     240 VM Committed                                    240 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             72 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLSTORENG (node 0)                 KB ---------------------------------------- ---------- VM Reserved                                     384 VM Committed                                    384 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                            680 MultiPage Allocator                            3848 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLCONNECTIONPOOL (node 0)         KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                            488 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLCLR (node 0)                     KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             8 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLSERVICEBROKER (node 0)            KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                            128 MultiPage Allocator                             544 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLHTTP (node 0)                     KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             8 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SNI (node 0)                         KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             16 MultiPage Allocator                             16 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_FULLTEXT (node 0)                    KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             24 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLXP (node 0)                     KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             16 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLQERESERVATIONS (node 0)         KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                         1312 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_XE_BUFFER (node 0)                 KB ---------------------------------------- ---------- VM Reserved                                    4224 VM Committed                                 4224 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             0 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SOSNODE (node 0)                     KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                         1008 MultiPage Allocator                         11136 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0)         KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             48 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      MEMORYCLERK_XE (node 0)                         KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             48 MultiPage Allocator                             168 2014-08-28 08:34:10.54 spid51      CACHESTORE_OBJCP (node 0)                        KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             8 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      CACHESTORE_SQLCP (node 0)                        KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                            272 MultiPage Allocator                            1568 2014-08-28 08:34:10.54 spid51      CACHESTORE_PHDR (node 0)                         KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                            168 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      CACHESTORE_XPROC (node 0)                        KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             16 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      CACHESTORE_TEMPTABLES (node 0)                 KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             16 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      CACHESTORE_NOTIF (node 0)                        KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages Allocated                            0 SM Reserved                                     0 SM Committed                                     0 SinglePage Allocator                             16 MultiPage Allocator                             0 2014-08-28 08:34:10.54 spid51      CACHESTORE_VIEWDEFINITIONS (node 0)             KB ---------------------------------------- ---------- VM Reserved                                     0 VM Committed                                     0 Locked Pages 

    Tuesday, September 2, 2014 7:39 AM
  • Hi Baraiya,

    I received your message regarding results of DBCC MEMORYSTATUS. But information was part of results. Please upload complete results of DBCC MEMORYSTATUS.

    As I mentioned earlier about collecting SQL Server error log, I’m wondering if you can provide it for our deep analysis. And about how to find SQL Server error log, you can refer to this article: Viewing the SQL Server Error Log (http://technet.microsoft.com/en-us/library/ms187885(v=sql.105).aspx).


    Best regards,
    Qiuyun Yu


    Wednesday, September 3, 2014 11:01 AM
  • Please upload complete content on skydrive. Make sure you upload it in read able format. Use Ctrl +T to get output in text format and paste it properly in Notepad otherwise its not readable

    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 Articles

    Wednesday, September 3, 2014 11:13 AM
    Moderator
  • next same error came i will definitely send u...

    now this error is not showing in error logs...

    Thanks

      
    Friday, September 5, 2014 12:08 PM