1

Last night I received the error on one of my servers of:

Failed to allocate reserved pages: FAIL_RESERVED_PAGE_ALLOCATION 2047

I know that this has already been discussed on here some but all resolutions seem to point to "move away from SQL Server 2012 SP1" and we are currently on SQL Server 2014 SP1 (CU1).

It appears as though we may be getting a similar error to this one: https://connect.microsoft.com/SQLServer/feedback/details/721641/sql-server-2012-rc0-error-failed-to-allocate-reserved-pages-fail-reserved-page-allocation-997-when-creating-columnstore-index-specify-a-large-maxdop

But again I'm unable to verify. I can say, almost for certain, that we were bulk inserting (from another database on another server through SSIS) data into a table that does have a Clustered Columnstore Index (CCI) on it. It should be noted however that the table with the CCI on it has no Binary Large Objects associated with the data types specific to that table.

At this point if anyone has any ideas or could point me in the right direction with more data supplied by me, please let me know. Thanks.

At request some of the memory dump below:

There is insufficient system memory in resource pool 'default' to run this query.
Error: 701<c/> Severity: 17<c/> State: 123.
Memory Broker Clerk (Buffer Pool) Pages
---------------------------------------- ----------
Total 100686446
Simulated 21076661
Simulation Benefit 0.0000000003
Internal Benefit 0
External Benefit 0
Value Of Memory 0.0000000032
Periodic Freed 0
Internal Freed 0
Memory Broker Clerk (Column store object pool) Pages
---------------------------------------- ----------
Total 101810
Simulated 1300541
Simulation Benefit 0.0000000032
Internal Benefit 0
External Benefit 0
Value Of Memory 0.0000000003
Periodic Freed 0
Internal Freed 126813764
MEMORYBROKER_FOR_XTP (default) KB
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 637413560
Future Allocations 0
Overall 817637472
Last Notification 1
MEMORYBROKER_FOR_HASHED_DATA_PAGES (default) KB
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 637413560
Future Allocations 0
Overall 817637472
Last Notification 1
MEMORYBROKER_FOR_RESERVE (default) KB
---------------------------------------- ----------
Allocations 226457432
Rate 56
Target Allocations 817637472
Future Allocations 0
Overall 817637472
Last Notification 1
MEMORYBROKER_FOR_STEAL (default) KB
---------------------------------------- ----------
Allocations 594992
Rate -152
Target Allocations 638008552
Future Allocations 0
Overall 817637472
Last Notification 1
MEMORYBROKER_FOR_CACHE (default) KB
---------------------------------------- ----------
Allocations 44448696
Rate -32
Target Allocations 681862256
Future Allocations 0
Overall 817637472
Last Notification 1
Memory Pool (default) KB
---------------------------------------- ----------
Allocations 271501120
Predicted 271501176
Private Target 0
Private Limit 0
Total Target 969051824
Total Limit 969051824
OOM Count 0
MEMORYBROKER_FOR_XTP (internal) KB
---------------------------------------- ----------
Allocations 5528
Rate 0
Target Allocations 637419088
Future Allocations 0
Overall 817637472
Last Notification 1
MEMORYBROKER_FOR_HASHED_DATA_PAGES (internal) KB
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 637413560
Future Allocations 0
Overall 817637472
Last Notification 1
MEMORYBROKER_FOR_COMMITTED (internal) KB
---------------------------------------- ----------
Allocations 57504696
Rate 0
Target Allocations 694918256
Future Allocations 0
Overall 817637472
Last Notification 1
MEMORYBROKER_FOR_RESERVE (internal) KB
---------------------------------------- ----------
Allocations 2097152
Rate 0
Target Allocations 639510712
Future Allocations 0
Overall 817637472
Last Notification 1
MEMORYBROKER_FOR_STEAL (internal) KB
---------------------------------------- ----------
Allocations 217936
Rate 64
Target Allocations 637631560
Future Allocations 0
Overall 817637472
Last Notification 1
MEMORYBROKER_FOR_CACHE (internal) KB
---------------------------------------- ----------
Allocations 311712
Rate 0
Target Allocations 637725272
Future Allocations 0
Overall 817637472
Last Notification 1
Memory Pool (internal) KB
---------------------------------------- ----------
Allocations 60137024
Predicted 60137088
Private Target 0
Private Limit 0
Total Target 969051824
Total Limit 969051824
OOM Count 0
Memory Pool Manager KB
---------------------------------------- ----------
Reserved Current 228554584
Reserved Limit 865968264
Big Gateway (default) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
Medium Gateway (default) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 12
Threshold 27221698218
Small Gateway (default) Value
---------------------------------------- ----------
Configured Units 128
Available Units 126
Acquires 2
Waiters 0
Threshold Factor 380000
Threshold 380000
Optimization Queue (default) Value
---------------------------------------- ----------
Overall Memory 837260771328
Target Memory 653320757248
Last Notification 1
Timeout 6
Early Termination Factor 5
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 128
Available Units 128
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 837260771328
Target Memory 652934717440
Last Notification 1
Timeout 6
Early Termination Factor 5
Remote Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 47847000
Current Max 47847000
Small Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 4
Waiting 0
Available 121518
Current Max 122880
Future Max 122880
Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 28
Waiting 0
Available 67388183
Current Max 95694000
Future Max 95694000
Physical Max 95694000
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Remote Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 47847000
Current Max 47847000
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 122880
Current Max 122880
Future Max 122880
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 1
Waiting 0
Available 74557616
Current Max 74819760
Future Max 74819760
Physical Max 95694000
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Global Memory Objects Pages
---------------------------------------- ----------
Resource 15568
Locks 5439892
XDES 1787
DirtyPageTracking 32
SETLS 32
SubpDesc Allocators 119
SE SchemaManager 10156
SE Column Metadata Cache 7788
SE Column Metadata Cache Store 45
SQLCache 777
Replication 34
ServerGlobal 66
XP Global 2
SortTables 3
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 5070
TotalPages 91639
InUsePages 3221
Buffer Pool Value
---------------------------------------- ----------
Database 100685724
Simulated 21076986
Target 101987493
Dirty 33014
In IO 31
Latched 1167
Page Life Expectancy 1482
OBJECTSTORE_XACT_CACHE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 7200
OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 4196352
VM Committed 4196352
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 43519144
OBJECTSTORE_LOCK_MANAGER (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
OBJECTSTORE_LOCK_MANAGER (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 581688
OBJECTSTORE_LOCK_MANAGER (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 23831776
OBJECTSTORE_LOCK_MANAGER (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16441160
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 4196352
VM Committed 4196352
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2664496
OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 552
OBJECTSTORE_SERVICE_BROKER (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 160
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 392
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3568
OBJECTSTORE_SNI_PACKET (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
OBJECTSTORE_SNI_PACKET (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 784
OBJECTSTORE_SNI_PACKET (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 840
OBJECTSTORE_SNI_PACKET (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 872
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1016
OBJECTSTORE_LBSS (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1136
OBJECTSTORE_LBSS (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 312
OBJECTSTORE_LBSS (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 280
OBJECTSTORE_LBSS (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 264
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 280
USERSTORE_QDSSTMT (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 448
USERSTORE_QDSSTMT (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
USERSTORE_QDSSTMT (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 416
USERSTORE_QDSSTMT (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 496
USERSTORE_SXC (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
USERSTORE_SXC (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 88
USERSTORE_SXC (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 112
USERSTORE_SXC (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 160
USERSTORE_SXC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
USERSTORE_OBJPERM (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 15904
USERSTORE_OBJPERM (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1128
USERSTORE_OBJPERM (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 14488
USERSTORE_OBJPERM (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 288

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.