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

    Question

  • Hi Friends,

    I'm experiencing a very annoying and hard to fix problem with my SQL2008, housed on Windows Server 2003 R3 Enterprise. Every couple of days my maintenance plans are failing with the following error message

    'There is insufficient system memory in resource pool 'internal' to run this query. BACKUP LOG is terminating abnormally'

    If i restart the server then it will resolve but within a few days it pops its ugly head up again. The machine has 4gb Ram and this problem has only just started happening.

    Has anyone else come across this before??? Oh and the specs are (SQL Server 2008 SP1 CU5, max memory is set at 4096Mb )

    Many thanks :)

    Mr J


    Monday, July 04, 2011 3:48 AM

Answers

  • Can you please share errorlog with us?


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Monday, July 04, 2011 3:53 AM
    Moderator

All replies

  • Can you please share errorlog with us?


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Monday, July 04, 2011 3:53 AM
    Moderator
  • Hello,

    The error logs show...

    2011-07-04 05:02:06.03 Backup      BACKUP failed to complete the command BACKUP LOG <<Name Removed>>. Check the backup application log for detailed messages.
    2011-07-04 05:02:09.78 spid130     Error: 701, Severity: 17, State: 17.
    2011-07-04 05:02:09.78 spid130     There is insufficient system memory in resource pool 'internal' to run this query.

    The Job logs show...

    Source: Back Up Database Task Execute SQL Task     Description: Executing the query "BACKUP LOG [<<Name Removed>>] TO  DISK = N'D:\SQLLog..." failed with the following error: "There is insufficient system memory in resource pool 'internal' to run this query.  BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Error: 2011-07-04 05:02:09.79     Code: 0xC002F210

    Many thanks :)

    Monday, July 04, 2011 4:15 AM
  • Try CU9 for SP1, check http://support.microsoft.com/kb/982854 KB article, however it's applicable for state 123.

    What's your min and max memory setting?

    Is resource governor turned on?

    Can you try to free cache and give a try for backup?

    Run DBCC MEMORYSTATUS() to check how much memory is allocated to each object in sql.


    Vidhya Sagar. Mark as Answer if it helps!
    Monday, July 04, 2011 7:35 AM
    Moderator
  • Hello :)

    The min is 0 and the max is set at 4096

    Resource governor is not on

    I have tried clearing the resource pool for 'default' and 'internal' and it still came up with the same error. I had to restart the server again so i can not provide the dbcc memorystatus() but i will do so once the problem raises again... i dont think you'll have to wait too long... sadly 

    Many thanks!!

    Tuesday, July 05, 2011 12:51 AM
  • As Vidhya mentioned DBCC MemoryStaus will give us hint about "which" memory clerk is eating memory but it would help if you share errorlog with us to check few more things (like CLR loading , extended proc loading) Feel free to remove confidential infromation and share.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Tuesday, July 05, 2011 1:20 AM
    Moderator
  • If i restart the server then it will resolve but within a few days it pops its ugly head up again. The machine has 4gb Ram and this problem has only just started happening.

    Has anyone else come across this before??? Oh and the specs are (SQL Server 2008 SP1 CU5, max memory is set at 4096Mb )


    I wonder what happens if you set max memory *down* to 3gb?

    Have to leave room for Windows y'know.

    Is this 64bit?

    Anything else besides SQL Server running on the server?

    Josh

     


    Tuesday, July 05, 2011 1:38 AM
  • Hello friends,

    The environment is SQL2008 R1 on Windows 2003 R3 Enterprise, so 32bit, the server is a clustered node with 2005 on it as well, but its only the 2008 thats experiencing this problem. Josh do you think the error refers to windows lacking the sufficient resources?? SQL gobbling up too much??

    Balmukund after searching through the logs i've noticed the following error/issue... shortly after this the backups start to fail as i posted in my previous entry.

    2011-07-03 07:02:02.04 spid2s      AppDomain 2 (<<Name Removed>>.dbo[runtime].1) is marked for unload due to memory pressure.
    2011-07-03 07:02:02.05 spid13s     AppDomain 2 (<<Name Removed>>.dbo[runtime].1) unloaded.
    2011-07-03 07:02:05.96 spid158      Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1114112
    2011-07-03 07:02:05.97 spid158    

    I very much appreciate the support

    Cheers :)

    Tuesday, July 05, 2011 2:43 AM
  • Balmukund unfortunately the memorystatus dump is too large for me to post... keep getting a time out error, if you give me a list of the memory objects you want me to post i'll be more than happy to provide.

    Cheers :)

    Tuesday, July 05, 2011 3:11 AM
  • Hello friends,

    The environment is SQL2008 R1 on Windows 2003 R3 Enterprise, so 32bit, the server is a clustered node with 2005 on it as well, but its only the 2008 thats experiencing this problem. Josh do you think the error refers to windows lacking the sufficient resources?? SQL gobbling up too much??

    Balmukund after searching through the logs i've noticed the following error/issue... shortly after this the backups start to fail as i posted in my previous entry.

    2011-07-03 07:02:02.04 spid2s      AppDomain 2 (<<Name Removed>>.dbo[runtime].1) is marked for unload due to memory pressure.
    2011-07-03 07:02:02.05 spid13s     AppDomain 2 (<<Name Removed>>.dbo[runtime].1) unloaded.
    2011-07-03 07:02:05.96 spid158      Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1114112
    2011-07-03 07:02:05.97 spid158    

    I very much appreciate the support

    Cheers :)


    You have multiple instances on the cluster - with this instance set to use all of the memory on that node?  No wonder you have resource issues - between both instances, there isn't any memory left for any other processes.

    How much memory is the 2005 instance configured to use, how much is actually used (use Perfmon to get Target Memory and Total Memory counters for each instance).

    With only 4GB of memory available on the system - I would not recommend running multiple instances unless you are very sure that the total memory usage of all instances won't ever exceed 3GB of total memory.  That is, one instance can be configured to use 2GB and the other 1GB - or any other combination that does not exceed 3GB (possibly 3.5GB - but that would be pushing it).

    Since this is x86 - check to see if you have /3GB switch set in the boot.ini.  If you do, remove it - as that is also causing issues here.


    Jeff Williams
    Tuesday, July 05, 2011 3:22 AM
  • Hi Jeff,

    I think i am at fault here, the server has a total off 24Gg and 4Gb has been assigned to SQL2008(1 instance) then i have 4 instances of SQL2005 with the mem max set at 2Gb x2, 3Gb x 1 and 4Gb x1. I have checked the boot.ini and no such /3Gb flag is located.

    Cheers

    Tuesday, July 05, 2011 3:44 AM
  • I think i am at fault here, the server has a total off 24Gg and 4Gb has been assigned to SQL2008(1 instance) then i have 4 instances of SQL2005 with the mem max set at 2Gb x2, 3Gb x 1 and 4Gb x1. I have checked the boot.ini and no such /3Gb flag is located.


    Windows also 32 bit?

    Not that it tells me the answer either way, just curious.

    Are you running any CLR code from inside SQL, or any old xp's, that might be leaking memory?

    Josh

     

    Tuesday, July 05, 2011 4:29 AM
  • Hi Josh,

    Yeah the server is 32bit, i am currently chasing a third party about a assembly they added to one of their DB's the very one that has the error i posted before.

    2011-07-03 07:02:02.04 spid2s      AppDomain 2 (<<Name Removed>>.dbo[runtime].1) is marked for unload due to memory pressure.
    2011-07-03 07:02:02.05 spid13s     AppDomain 2 (<<Name Removed>>.dbo[runtime].1) unloaded.
    2011-07-03 07:02:05.96 spid158      Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1114112

    The server isn't really want you would call pushin it to the max either, just has one publisher and subscripter for replication, a bunch of jobs that run during the day(nothing heavy) and a standard maintenance plan.

    How can i check if a extended SP is leakin mem??

    Cheers :)

    Tuesday, July 05, 2011 5:02 AM
  • It seems like MTL pressure.. Could you please post the command which you are using to take the backup. Need to check the buffersize and transfersize.

     

    I have seen this kind of issue of "backup failure" due to the value of these parameters to be high. or else it can also be due to memory leak in MTL region for which there are other steps to be followed. 

     


    Harsh Chawla Personal Blog:-http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Tuesday, July 05, 2011 6:05 AM
  • Hi Harsh,

    The command script to perform the backup is are follows

    BACKUP LOG [<<Name Removed>>] TO  DISK = N'<<Path Removed>>.trn' WITH NOFORMAT, NOINIT,  NAME = N'<<Name Removed>>', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10
    GO
    declare @backupSetId as int
    select @backupSetId = position from msdb..backupset where database_name=N'<<Name Removed>>' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'<<Name Removed>>' )
    if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''<<Name Removed>>'' not found.', 16, 1) end
    RESTORE VERIFYONLY FROM  DISK = N'<<Path Removed>>.trn' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
    GO

    Can you explain what you mean by MTL pressure?

    Thanks :)

    Tuesday, July 05, 2011 6:30 AM
  • It seems like you are using default settings(buffersize and transfersize) for taking the backup. So, there may be someother issue and also from the error log snippet it seems that you are using CLR.  

    So, normally in these kind of scenarios we can also add -g384  or -g512 to the startup parameters to increase the MTL region. for complete explanation of these kind of issues please check this post for this error : - http://blogs.msdn.com/b/sqlserverfaq/archive/2010/02/16/how-to-find-who-is-using-eating-up-the-virtual-address-space-on-your-sql-server.aspx

    For explanation of MTL please check the article : - http://blogs.msdn.com/b/slavao/archive/2006/03/14/551394.aspx

     





    Tuesday, July 05, 2011 8:21 AM
  • How can i check if a extended SP is leakin mem??

    I don't actually know offhand, but I think that's the next thing you need to check.

    Anyway you can avoid calling it for a while, just to see if that helps?

    Josh

     

    Tuesday, July 05, 2011 3:57 PM
  • To find out the leak, you will have to take the dumps by enabling the trace flag 2551,8004 and open a case to find out which dll is causing the leak.

    Before doing that I would say please follow the article in my previous post.

     


    Harsh Chawla Personal Blog:-http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Tuesday, July 05, 2011 5:21 PM
  • Just to be ensure of your configuration, could you please run the query below and share with us the result

     


    SET NOCOUNT ON;  
    CREATE TABLE #temp (indexName int, logDate varchar(50), Internal_value varchar(50), value text);
    INSERT #temp exec master..xp_msver;
    DECLARE @properties VARCHAR(8000)
    SET @properties = ''
    SELECT @properties = @properties + COALESCE(logDate +  ':' + CAST(value as varchar) +  char(9) +  char(9), '') FROM #temp WHERE indexName IN(4,16,19) ;
    SELECT @properties = @properties + COALESCE(name +  ':' + CAST(value_in_use as varchar) +  char(9) +  char(9), '') FROM sys.configurations  WHERE configuration_id IN (1543,1544,1548)
    SELECT @properties
    DROP  TABLE #temp;
    SELECT CAST(SERVERPROPERTY('Edition')  as varchar) + ' | ' + CAST(SERVERPROPERTY('ProductVersion' ) as varchar)


    Regarding the DBCC Memory Status, could you run it some time before that the issue occurs, to see which counter rise up quickly 


    Michel DEGREMONT , my blog SQL Server
    Tuesday, July 05, 2011 6:07 PM
  • Hi Michel,

    The output is as follows

    Platform:NT INTEL X86  ProcessorCount:16  PhysicalMemory:24566 (25758801920)  min server memory (MB):0  max server memory (MB):4096  awe enabled:0 

    Enterprise Edition | 10.0.2746.0

    -----

    Harsh, its my understanding that if i do use the -g option to assign more mem to MTL a restart is in order, so i'll hold off on this until the problem reappears, i guess in the mean time its my objective to find the root cause rather than a bandaid(for which i am grateful to now know). I am new to all this memory issues can you advise which subheading under dbcc memorystatus i should be paying attention too??

    Many many thanks :)

    Wednesday, July 06, 2011 2:10 AM
  • Morning,

     

    1. Regarding the memory configuration, there are something wrong. You are in 32Bit, so you process is not able to get over 2Go by default. You have to enable AWE (with SP_configure + Enable Lock page in memory to your SQL server account). Then you instance will be able to take 4go of memory.

    I'm pretty sure that you instance doesn't consume more than 1,6 Go. You can verify with the perfmon or the command below (could you share the result).

     SELECT CAST(counter_name  as varchar),cntr_value
    FROM sys.[dm_os_performance_counters] 
    WHERE [counter_name] LIKE '%Server Memory%'

     

    2. Just for the test and only for the test, put directly -g1000 and check if the issue occurs again.

     

    Looking forward to reading the memory status,

    Cheers,

     


    Michel DEGREMONT , my blog SQL Server
    Wednesday, July 06, 2011 9:30 AM
  •  

    In the case of MTL pressure, I would prefer looking at clerks which consume more mulitpage allocator memory.  But that may or may not point to the cause of the problem. 

     

    If it's a leak in that taking a dump will be one of the best options to find out the RCA.

    For learning about memory in SQL server I would recommend you to read the blogs from Slava : - http://blogs.msdn.com/b/slavao/

     

    And if you have any questions, feel free to post a question.

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Wednesday, July 06, 2011 9:43 AM
  • Hi Michel,

    Thanks for the heads up, the output of the script you forwarded is as follows;

    Target Server Memory (KB)      1572672
    Total Server Memory (KB)       1572576

    I've just started running the following scripts to check the mem consumed by procedures

    SELECT SUM(PAGESUSED)*8/1024 'MB of MemToLeave memory consumed by procedures'
    FROM MASTER.DBO.SYSCACHEOBJECTS WHERE PAGESUSED >1

    with the following results 

    07/07/2011 13:00
    415Mb
    07/07/2011 14:00
    595Mb

    I have not required to restart the server yet so i have not implemented the -g option as yet.

    ------

    Harsh which mem clerk would you like to me post? Everytime i try to paste the whole dataset into here get a timeout error :(

    Thanks :)

    Thursday, July 07, 2011 5:01 AM
  •  

    So, SQL is configured for  only 1.5go and not 4go as expected.

    Could you enable AWE with the command below :

     

    sp_configure

    'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'awe enabled', 1
    RECONFIGURE
    GO
    sp_configure 'show advanced options', 0
    RECONFIGURE
    GO

     

     


    Michel DEGREMONT | Microsoft | PFE Team , my blog SQL Server
    Thursday, July 07, 2011 11:53 AM
  • Hello:

    I am not sure if your memory issue is resolved. but this got fixed in Service Pack-1.

    Thanks

    Wednesday, September 14, 2011 2:56 PM
  • Today I had same issue, backup failed and in error log there was a message "There is insufficient system memory in resource pool 'internal' to run this query."

    Server has total 4gb ram and almost 3.5 gb was allocated to sql server 2008. There is nothing else running on the server. 

    By giving breathing space to operating system (setting sql max memory to 3 gb) .... will that help?


    Ankit H Peshwaria

    Wednesday, March 14, 2012 2:26 PM
  • Could you please share the snippet of the error log of that time?  We will need see the error messages before and after that error.

    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Wednesday, March 14, 2012 2:30 PM
  • Setting max memory seems it worked for me

    below is a section of the error log. I have replaced actual database names with Database1 & Database2

    03/14/2012 06:01:04,Backup,Unknown,Log was backed up. Database: Database1<c/> creation date(time): 2010/11/22(14:16:10)<c/> first LSN: 228:110976:1<c/> last LSN: 228:121281:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Database1\Database1_backup_2012_03_14_060003_0576963.trn'}). This is an informational message only. No user action is required.

    03/14/2012 06:00:45,Backup,Unknown,Log was backed up. Database: Database2<c/> creation date(time): 2010/11/22(10:43:13)<c/> first LSN: 77052:5040:1<c/> last LSN: 77183:4213:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Database2\Database2_backup_2012_03_14_060003_0313281.trn'}). This is an informational message only. No user action is required.

    03/14/2012 05:54:00,Backup,Unknown,Database backed up. Database: Database1<c/> creation date(time): 2010/11/22(14:16:10)<c/> pages dumped: 226699<c/> first LSN: 228:121257:37<c/> last LSN: 228:121274:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Database1_backup_2012_03_14_055329_2164015.bak'}). This is an informational message only. No user action is required.

    03/14/2012 05:53:27,spid102,Unknown,DBCC CHECKDB (Database1) WITH no_infomsgs executed by DOMAIN\SERVICE_AC found 0 errors and repaired 0 errors. Elapsed time: 0 hours 2 minutes 15 seconds.  Internal database snapshot has split point LSN = 000000e4:0001d9a3:0001 and first LSN = 000000e4:0001d9a2:0001.

    03/14/2012 05:51:12,spid82,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.

    03/14/2012 05:51:12,spid82,Unknown,Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

    03/14/2012 05:46:07,spid79,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.

    03/14/2012 05:46:07,spid79,Unknown,Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

    03/14/2012 05:46:06,spid79,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

    03/14/2012 05:46:03,spid79,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

    03/14/2012 05:33:06,spid79,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

    03/14/2012 05:32:50,spid79,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.

    03/14/2012 05:32:50,spid79,Unknown,Configuration option 'max server memory (MB)' changed from 2147483647 to 3037. Run the RECONFIGURE statement to install.

    03/14/2012 05:32:50,spid79,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.

    03/14/2012 05:32:50,spid79,Unknown,Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

    03/14/2012 04:04:16,spid115,Unknown,DBCC CHECKDB (Database1) WITH no_infomsgs executed by DOMAIN\SERVICE_AC terminated abnormally due to error state 5. Elapsed time: 0 hours 2 minutes 41 seconds.

    03/14/2012 04:04:16,spid80,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.

    03/14/2012 04:04:16,spid80,Unknown,Error: 701<c/> Severity: 17<c/> State: 65.

    03/14/2012 04:04:16,spid102,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.

    03/14/2012 04:04:16,spid102,Unknown,Error: 701<c/> Severity: 17<c/> State: 65.

    03/14/2012 04:04:16,Logon,Unknown,There was a memory allocation failure during connection establishment. Reduce nonessential memory load<c/> or increase system memory. The connection has been closed. [CLIENT: 172.20.12.23]

    03/14/2012 04:04:16,Logon,Unknown,Error: 17803<c/> Severity: 20<c/> State: 13.

    03/14/2012 04:04:16,spid79,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.

    03/14/2012 04:04:16,spid79,Unknown,Error: 701<c/> Severity: 17<c/> State: 65.

    03/14/2012 04:04:16,spid116,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.

    03/14/2012 04:04:16,spid116,Unknown,Error: 701<c/> Severity: 17<c/> State: 130.

    03/14/2012 04:04:16,spid62,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.

    03/14/2012 04:04:16,spid62,Unknown,Error: 701<c/> Severity: 17<c/> State: 65.


    Ankit H Peshwaria

    Wednesday, March 14, 2012 3:35 PM
  • It seems to be primarily Bpool pressure and also some bit of MTL memory pressure. Could you please share the output of below queries:

     select
     @@version as [Version],
    SERVERPROPERTY('IsClustered') as [Clustered]

    go

    select SUM(single_page_allocator) "single_page_allocator" ,SUM(multi_page_allocator) "multi_page_allocator" ,SUM([Awe allocated]) "Awe allocated"
     from (
     select SUM(single_pages_kb) "single_page_allocator",SUM(awe_allocated_kb) "Awe allocated",SUM(multi_pages_kb) "multi_page_allocator",type from sys.dm_os_memory_clerks
     group by type ) a

    go

    select SUM(single_pages_kb) "single_page_allocator",SUM(awe_allocated_kb) "Awe allocated",SUM(multi_pages_kb) "multi_page_allocator",type from sys.dm_os_memory_clerks
      where single_pages_kb>200
     group by type

    go


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Thursday, March 15, 2012 6:31 AM
  • Version                                                                                                                                                                                                                                                          Clustered
    ----------------------------------------------------------------------------------------------------------------------------
    Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
        Sep 16 2010 19:43:16
        Copyright (c) 1988-2008 Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)
                                                                   0

    single_page_allocator multi_page_allocator Awe allocated
    --------------------- -------------------- --------------------
    288848                30688                0

    single_page_allocator Awe allocated        multi_page_allocator type
    --------------------- -------------------- -------------------- ------------------------------------------------------------
    27360                 0                    904                  CACHESTORE_OBJCP
    10568                 0                    16                   CACHESTORE_PHDR
    169160                0                    2952                 CACHESTORE_SQLCP
    2664                  0                    0                    CACHESTORE_SYSTEMROWSET
    256                   0                    0                    MEMORYCLERK_BHF
    272                   0                    16                   MEMORYCLERK_SNI
    3488                  0                    12336                MEMORYCLERK_SOSNODE
    22552                 0                    1056                 MEMORYCLERK_SQLCONNECTIONPOOL
    1816                  0                    2120                 MEMORYCLERK_SQLGENERAL
    240                   0                    896                  MEMORYCLERK_SQLOPTIMIZER
    2304                  0                    4024                 MEMORYCLERK_SQLSTORENG
    4520                  0                    184                  MEMORYCLERK_XE
    9000                  0                    0                    OBJECTSTORE_LOCK_MANAGER
    6784                  0                    16                   OBJECTSTORE_SNI_PACKET
    6792                  0                    0                    USERSTORE_DBMETADATA
    6320                  0                    128                  USERSTORE_SCHEMAMGR
    544                   0                    0                    USERSTORE_SXC
    11152                 0                    2552                 USERSTORE_TOKENPERM

    Ankit H Peshwaria


    • Edited by Ankit P Thursday, March 15, 2012 7:29 AM
    Thursday, March 15, 2012 7:28 AM
  • Hello Ankit,

    For now, there doesn't seem to be any problem. But I suspect CACHESTORE_SQLCP can cause the issue based on it's current size.  It's too early to conclude.

    Best thing you could do for now is, monitoring the SQL server memory. If the memory of SQL goes above 1.5 GB then run the last 2 queries again to check the status.

    If SQLCP is still high, please check the procedure cache pollution by running the query:-

    WITH cached_plans (cacheobjtype, objtype, usecounts, size_in_bytes, dbid, objectid, short_qry_text) AS
    (
    SELECT p.cacheobjtype, p.objtype, p.usecounts, size_in_bytes, s.dbid, s.objectid,
    CONVERT (nvarchar(100), REPLACE (REPLACE (
    CASE
    -- Special cases: handle NULL s.[text] and 'SET NOEXEC'
    WHEN s.[text] IS NULL THEN NULL
    WHEN CHARINDEX ('noexec', SUBSTRING (s.[text], 1, 200)) > 0 THEN SUBSTRING (s.[text], 1, 40)
    -- CASE #1: sp_executesql (query text passed in as 1st parameter)
    WHEN (CHARINDEX ('sp_executesql', SUBSTRING (s.[text], 1, 200)) > 0)
    THEN SUBSTRING (s.[text], CHARINDEX ('exec', SUBSTRING (s.[text], 1, 200)), 60)
    -- CASE #3: any other stored proc -- strip off any parameters
    WHEN CHARINDEX ('exec ', SUBSTRING (s.[text], 1, 200)) > 0
    THEN SUBSTRING (s.[text], CHARINDEX ('exec', SUBSTRING (s.[text], 1, 4000)),
    CHARINDEX (' ', SUBSTRING (SUBSTRING (s.[text], 1, 200) + ' ', CHARINDEX ('exec', SUBSTRING (s.[text], 1, 500)), 200), 9) )
    -- CASE #4: stored proc that starts with common prefix 'sp%' instead of 'exec'
    WHEN SUBSTRING (s.[text], 1, 2) IN ('sp', 'xp', 'usp')
    THEN SUBSTRING (s.[text], 1, CHARINDEX (' ', SUBSTRING (s.[text], 1, 200) + ' '))
    -- CASE #5: ad hoc UPD/INS/DEL query (on average, updates/inserts/deletes usually
    -- need a shorter substring to avoid hitting parameters)
    WHEN SUBSTRING (s.[text], 1, 30) LIKE '%UPDATE %' OR SUBSTRING (s.[text], 1, 30) LIKE '%INSERT %'
    OR SUBSTRING (s.[text], 1, 30) LIKE '%DELETE %'
    THEN SUBSTRING (s.[text], 1, 30)
    -- CASE #6: other ad hoc query
    ELSE SUBSTRING (s.[text], 1, 45)
    END
    , CHAR (10), ' '), CHAR (13), ' ')) AS short_qry_text
    FROM sys.dm_exec_cached_plans p
    CROSS APPLY sys.dm_exec_sql_text (p.plan_handle) s
    )
    SELECT COUNT(*) AS plan_count, SUM (size_in_bytes) AS total_size_in_bytes,
    cacheobjtype, objtype, usecounts, dbid, objectid, short_qry_text
    FROM cached_plans
    GROUP BY cacheobjtype, objtype, usecounts, dbid, objectid, short_qry_text
    HAVING COUNT(*) > 100
    ORDER BY COUNT(*) DESC
    RAISERROR ('', 0, 1) WITH NOWAIT

    and share the output. It'll be good to reduce max server memory to 3 GB to avoid paging at the OS level.

    Also, please create a new thread in the forum (put the refernce of this thread) once you notice the same issue again.

    HTH!


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Thursday, March 15, 2012 7:51 AM
  • Hi Harsh,

    As I mentioned earlier Max Memory 3 GB is working for me. And the output of the sql statement is 

    plan_count  total_size_in_bytes cacheobjtype                                       objtype              usecounts   dbid   objectid    short_qry_text
    ----------- ------------------- -------------------------------------------------- -------------------- ----------- ------ ----------- ------------------------------
    490         12042240            Compiled Plan                                      Adhoc                1           NULL   NULL        UPDATE msdb.dbo.sysjobschedule

    And Thank You for your Time :)


    Ankit H Peshwaria

    Friday, March 16, 2012 9:12 AM
  • Please share the output of the queries:

    select SUM(single_page_allocator) "single_page_allocator" ,SUM(multi_page_allocator) "multi_page_allocator" ,SUM([Awe allocated]) "Awe allocated"
    from (
    select SUM(single_pages_kb) "single_page_allocator",SUM(awe_allocated_kb) "Awe allocated",SUM(multi_pages_kb) "multi_page_allocator",type from sys.dm_os_memory_clerks
    group by type ) a

    go

    select SUM(single_pages_kb) "single_page_allocator",SUM(awe_allocated_kb) "Awe allocated",SUM(multi_pages_kb) "multi_page_allocator",type from sys.dm_os_memory_clerks
      where single_pages_kb>200
    group by type

    go


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Friday, March 16, 2012 9:43 AM
  • plan_count  total_size_in_bytes cacheobjtype         objtype   usecounts   dbid   objectid    short_qry_text
    ----------- ------------------- -------------------- --------- ----------- ------ ----------- ------------------------------
    546         13418496            Compiled Plan        Adhoc     1           NULL   NULL        UPDATE msdb.dbo.sysjobschedule

    single_page_allocator multi_page_allocator Awe allocated
    --------------------- -------------------- --------------------
    363016                30512                0

    single_page_allocator Awe allocated        multi_page_allocator type
    --------------------- -------------------- -------------------- ---------------------------
    28120                 0                    976                  CACHESTORE_OBJCP
    11136                 0                    16                   CACHESTORE_PHDR
    253264                0                    3352                 CACHESTORE_SQLCP
    2664                  0                    0                    CACHESTORE_SYSTEMROWSET
    288                   0                    0                    MEMORYCLERK_BHF
    256                   0                    16                   MEMORYCLERK_SNI
    3432                  0                    12336                MEMORYCLERK_SOSNODE
    9960                  0                    424                  MEMORYCLERK_SQLCONNECTIONPOOL
    1816                  0                    2120                 MEMORYCLERK_SQLGENERAL
    240                   0                    896                  MEMORYCLERK_SQLOPTIMIZER
    2288                  0                    4024                 MEMORYCLERK_SQLSTORENG
    4912                  0                    168                  MEMORYCLERK_XE
    9000                  0                    0                    OBJECTSTORE_LOCK_MANAGER
    6784                  0                    16                   OBJECTSTORE_SNI_PACKET
    6800                  0                    0                    USERSTORE_DBMETADATA
    6424                  0                    128                  USERSTORE_SCHEMAMGR
    496                   0                    0                    USERSTORE_SXC
    12064                 0                    2552                 USERSTORE_TOKENPERM

    (18 row(s) affected)

    Ankit H Peshwaria

    Friday, March 16, 2012 2:34 PM
  • Hi All,

    Hope you all are doing good.

    I am novice in Database.

    I am also facing this same problem again and again that there is not enough memory in the resource pool "default" to run this query.

    Please find the details of my Server: RAM= 48 Gb, MS Server 2003 32 bit. MS SQL server 2008 r2 Enterprise.

    Lots of queries are executed on this 24 * 7. After running for 10-15 odd days i start receiving this problem. I use to restart the MSSQL Service and again after 10-15 days i start receiving this problem. Initially I thought I have to clear the cache but after clearing cache still i am facing the problem.

    I use to fire this query to check the health

    select * from sys.dm_resource_governor_resource_pools where name='default'

    O/P

    max_memory_kb used_memory_kb
    1494032 302680

    This used memory keeps on increasing and get reset after i restart the service.

    Any help related to this will be highly appreciated as this is very painful.

    Please explain from very basics as I do not have any idea about DBA. 

    Thanking all of you in advance :)

    Regards

    Sudhanshoo



    Tuesday, August 14, 2012 8:01 PM
  • Could you please share the output of the below command:

    sp_configure 'show advanced options', 1
    RECONFIGURE
     GO
    sp_configure 'Max server memory'
     GO
    sp_configure 'Awe'
     GO

    You may want to reach this blog to understand, how to troubleshoot memory pressure for 32-bit SQL server environments : - http://dbcouncil.net/2012/07/09/sql-server-how-can-you-say-its-memory-pressure/


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog



    Wednesday, August 15, 2012 7:56 AM
  • Hi Harsh,

    Thanks for your prompt reply. Please find output of the query below.

    name minimum maximum config_value run_value
    max server memory (MB) 16 2147483647 594748364 594748364

    name minimum maximum config_value run_value
    awe enabled 0 1 0 0

    Thanks / Regards

    Sudhanshoo Sharda


    • Edited by Sudhanshoo Wednesday, August 15, 2012 9:33 AM Format not coming properly
    Wednesday, August 15, 2012 9:29 AM
  • As you mentioned earlier, that you have 32 bit SQL server and you have 48 Gb of RAM. I would recommend you to enable the AWE first of all so that SQL can use more than 1.6 Gb for data and index pages.   http://msdn.microsoft.com/en-us/library/ms190673(v=sql.105).aspx

          Also, set SQL server max server memory to 20Gb or so and see if that helps. if possible, please share the error messages of the time when issue occurred and before you restarted SQL server.

     

     


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog

    Wednesday, August 15, 2012 9:52 AM
  • Hi,

    I executed the following command: -

    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'awe enabled', 1
    RECONFIGURE
    GO

    After that I executed the following command

    sp_configure 'min server memory', 5120
    RECONFIGURE
    GO
    sp_configure 'max server memory', 20480
    RECONFIGURE
    GO

    To set minimum server memory as 5Gb & Maximum as 20 Gb & restarted the MSSQL server service hoping that i will see memory increased of resource governor.

    But the memory has decreased, please check the attached image for the same.

    I was thinking that max_memory_kb will increase but it has decreased. Please explain if I am missing something.

    Thanks You

    Sudhanshoo


    Wednesday, August 15, 2012 10:40 AM
  • Hi All,

    Still I am facing this issue after some time regularly. 

    Can issue is related to 32 bit??  As I check one of my server with 64 bit, its maximum_memory_kb was 40 GB.

    Please provide the way forward.

    Any help will be highly appreciated.

    Thursday, November 29, 2012 7:32 AM
  • Hi All,

    Iam also facing same problem with sql server 2008 r2.

    could any one help me to resolve this please.

    thnx

    Friday, July 05, 2013 11:36 AM
  • I am getting same error while my maintenance jobs are running. we have SQL server 2008R2 SP2 still need to apply this hotfix or it is part of SP2?

    Please Mark As Answer if it is helpful. \\Aim To Inspire Rather to Teach A.Shah

    Friday, June 13, 2014 1:58 PM