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

Hi

I have a .NET application executing a stored procedure on a SQL Server 2008 SP1 64 bit instance (version 10.0.2531.0), and getting the following exception:

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

Maximum server memory is set to 4096MB. The server has 4GB of physical RAM available, and is running Windows Server 2008 R2 x64.

The SQL Server instance is only consuming approx 1.5GB RAM and there is 15+% physical RAM unused.

The messages in error.log are as follows:

2010-09-25 03:28:38.15 spid51    Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2010-09-25 03:28:38.15 spid51   
Memory Manager                  KB
---------------------------------------- ----------
VM Reserved                 4277792
VM Committed                1532212
Locked Pages Allocated              0
Reserved Memory                1024
Reserved Memory In Use              0
2010-09-25 03:28:38.15 spid51   
Memory node Id = 0                KB
---------------------------------------- ----------
VM Reserved                 4276704
VM Committed                1531184
Locked Pages Allocated              0
MultiPage Allocator              38992
SinglePage Allocator            1371648
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SQLGENERAL (node 0)         KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              2280
MultiPage Allocator              2360
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SQLBUFFERPOOL (node 0)        KB
---------------------------------------- ----------
VM Reserved                 4214784
VM Committed                1470976
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               0
MultiPage Allocator               400
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SQLQUERYEXEC (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
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SQLOPTIMIZER (node 0)        KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator            1330432
MultiPage Allocator              16584
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SQLUTILITIES (node 0)        KB
---------------------------------------- ----------
VM Reserved                   480
VM Committed                  480
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              128
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SQLSTORENG (node 0)         KB
---------------------------------------- ----------
VM Reserved                  2816
VM Committed                  2816
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              3000
MultiPage Allocator              4728
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SQLCONNECTIONPOOL (node 0)      KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              424
MultiPage Allocator                0
2010-09-25 03:28:38.15 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
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SQLSERVICEBROKER (node 0)      KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              144
MultiPage Allocator               544
2010-09-25 03:28:38.15 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
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SNI (node 0)             KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               8
MultiPage Allocator               16
2010-09-25 03:28:38.15 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
2010-09-25 03:28:38.15 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
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_BHF (node 0)             KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              1048
MultiPage Allocator                0
2010-09-25 03:28:38.15 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
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_HOST (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
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_SOSNODE (node 0)           KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              4304
MultiPage Allocator              11248
2010-09-25 03:28:38.15 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
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_FSAGENT (node 0)           KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               8
MultiPage Allocator               16
2010-09-25 03:28:38.15 spid51   
MEMORYCLERK_XE (node 0)             KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               72
MultiPage Allocator               168
2010-09-25 03:28:38.15 spid51   
CACHESTORE_OBJCP (node 0)            KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              1544
MultiPage Allocator               152
2010-09-25 03:28:38.15 spid51   
CACHESTORE_SQLCP (node 0)            KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              1272
MultiPage Allocator              1568
2010-09-25 03:28:38.15 spid51   
CACHESTORE_PHDR (node 0)             KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              9568
MultiPage Allocator               80
2010-09-25 03:28:38.15 spid51   
CACHESTORE_XPROC (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
2010-09-25 03:28:38.15 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
2010-09-25 03:28:38.15 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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_VIEWDEFINITIONS (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_XMLDBTYPE (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_XMLDBELEMENT (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_XMLDBATTRIBUTE (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_BROKERTBLACS (node 0)         KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               40
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
CACHESTORE_BROKERKEK (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_BROKERDSH (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_BROKERUSERCERTLOOKUP (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_BROKERRSB (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_BROKERREADONLY (node 0)        KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               32
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
CACHESTORE_BROKERTO (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_EVENTS (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_SYSTEMROWSET (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
2010-09-25 03:28:38.15 spid51   
CACHESTORE_CONVPRI (node 0)           KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               32
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
CACHESTORE_FULLTEXTSTOPLIST (node 0)       KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               32
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
USERSTORE_SCHEMAMGR (node 0)           KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator             10952
MultiPage Allocator               256
2010-09-25 03:28:38.15 spid51   
USERSTORE_DBMETADATA (node 0)          KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              2672
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
USERSTORE_TOKENPERM (node 0)           KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              328
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
USERSTORE_OBJPERM (node 0)            KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              520
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
USERSTORE_SXC (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
2010-09-25 03:28:38.15 spid51   
OBJECTSTORE_LBSS (node 0)            KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               64
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
OBJECTSTORE_SNI_PACKET (node 0)         KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator               80
MultiPage Allocator               56
2010-09-25 03:28:38.15 spid51   
OBJECTSTORE_SERVICE_BROKER (node 0)       KB
---------------------------------------- ----------
VM Reserved                    0
VM Committed                   0
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              424
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
OBJECTSTORE_LOCK_MANAGER (node 0)        KB
---------------------------------------- ----------
VM Reserved                  4096
VM Committed                  4096
Locked Pages Allocated              0
SM Reserved                    0
SM Committed                   0
SinglePage Allocator              1808
MultiPage Allocator                0
2010-09-25 03:28:38.15 spid51   
Buffer Pool                  Value
---------------------------------------- ----------
Committed                  180480
Target                    180480
Database                    8974
Dirty                      367
In IO                       0
Latched                      0
Free                       50
Stolen                    171456
Reserved                     0
Visible                   180480
Stolen Potential                 0
Limiting Factor                 12
Last OOM Factor                  0
Page Life Expectancy              607
2010-09-25 03:28:38.15 spid51   
Process/System Counts             Value
---------------------------------------- ----------
Available Physical Memory        1106481152
Available Virtual Memory         8791447781376
Available Paging File          5191540736
Working Set               1557618688
Percent of Committed Memory in WS        99
Page Faults                106802455
System physical memory high            1
System physical memory low            0
Process physical memory low            1
Process virtual memory low            0
2010-09-25 03:28:38.15 spid51   
Procedure Cache                Value
---------------------------------------- ----------
TotalProcs                    1
TotalPages                   1776
InUsePages                    1
2010-09-25 03:28:38.15 spid51   
Global Memory Objects             Pages
---------------------------------------- ----------
Resource                    342
Locks                      226
XDES                       27
SETLS                       4
SE Dataset Allocators               8
SubpDesc Allocators                4
SE SchemaManager                660
SE Column Metadata Cache            732
SQLCache                    214
Replication                    2
ServerGlobal                   50
XP Global                     2
SortTables                    2
2010-09-25 03:28:38.15 spid51   
Query Memory Objects (internal)        Value
---------------------------------------- ----------
Grants                      0
Waiting                      0
Available                   3590
Current Max                  3590
Future Max                   3590
Physical Max                 134975
Next Request                   0
Waiting For                    0
Cost                       0
Timeout                      0
Wait Time                     0
2010-09-25 03:28:38.15 spid51   
Small Query Memory Objects (internal)     Value
---------------------------------------- ----------
Grants                      0
Waiting                      0
Available                    640
Current Max                   640
Future Max                   640
2010-09-25 03:28:38.15 spid51   
Optimization Queue (internal)         Value
---------------------------------------- ----------
Overall Memory              1185103872
Target Memory               978927616
Last Notification                 2
Timeout                      6
Early Termination Factor             5
2010-09-25 03:28:38.15 spid51   
Small Gateway (internal)           Value
---------------------------------------- ----------
Configured Units                 16
Available Units                 15
Acquires                     1
Waiters                      0
Threshold Factor               380000
Threshold                  380000
2010-09-25 03:28:38.15 spid51   
Medium Gateway (internal)           Value
---------------------------------------- ----------
Configured Units                 4
Available Units                  3
Acquires                     1
Waiters                      0
Threshold Factor                 12
Threshold                 81577301
2010-09-25 03:28:38.15 spid51   
Big Gateway (internal)            Value
---------------------------------------- ----------
Configured Units                 1
Available Units                  0
Acquires                     1
Waiters                      0
Threshold Factor                 8
Threshold                 122365952
2010-09-25 03:28:38.15 spid51   
Memory Pool Manager              Pages
---------------------------------------- ----------
Reserved Current                 0
Reserved Limit                  2
2010-09-25 03:28:38.15 spid51   
Memory Pool (internal)            Pages
---------------------------------------- ----------
Allocations                 171454
Predicted                  208395
Private Target                  0
Private Limit                   0
Total Target                 171456
Total Limit                 171456
OOM Count                   30141
2010-09-25 03:28:38.15 spid51   
MEMORYBROKER_FOR_CACHE (internal)       Pages
---------------------------------------- ----------
Allocations                  2512
Rate                       -6
Target Allocations               4520
Future Allocations                0
Overall                   144666
Last Notification                 0
2010-09-25 03:28:38.15 spid51   
MEMORYBROKER_FOR_STEAL (internal)       Pages
---------------------------------------- ----------
Allocations                 168942
Rate                      3198
Target Allocations              119498
Future Allocations                0
Overall                   144666
Last Notification                 2
2010-09-25 03:28:38.15 spid51   
MEMORYBROKER_FOR_RESERVE (internal)      Pages
---------------------------------------- ----------
Allocations                    0
Rate                       0
Target Allocations               4520
Future Allocations              33743
Overall                   144666
Last Notification                 0
2010-09-25 03:28:38.21 spid51   Error: 701, Severity: 17, State: 123.
2010-09-25 03:28:38.21 spid51   There is insufficient system memory in resource pool 'internal' to run this query.

Any help appreciated – really don’t know where to go from here.

Thanks

Tom

Are you using resource  governor?I would definetely set Maximum Server memory  smaller if you have 4GB of phisical memory, set Sql Server to Maximum 3GB, btw if from your obbservation Sql Server consumes 1.5GB maybe you can restrict it to 2GB.You should also consider using -g startup parameter which would enable to reserve more memory than default 256MB for memory outside the pool.http://msdn.microsoft.com/en-us/library/ms190737.aspx/

Hi KrystianResource governor is disabled.I have tried different values for Maximum Server Memory, from the default (very high number) down to 2GB.I thought the -g startup parameters were only useful for 32 bit instances of SQL Server – is this incorrect? Also the page you referenced states:Use the default for the -g parameter unless you see any of the following warnings in the SQL Server error log:”Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE “”Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT ” I am not seeing either of those messages in the error log.Thanks

Hi Tom,MemToLeave, -g switches and such are pretty irrelevant on 64-bit sql  Server.CSS SQL server  Engineers Blog Post64-bit SQL Server has 8TB of VAS (virtual address space) to play with on x64, 7TB on Itanium. Even on a server with 512GB RAM – yes GB – the buffer pool  uses 0.5TB of VAS. That leaves at least 6.5TB of VAS for other stuff!