we have a Data Warehouse server installed with an instance of SQL Server 2014 Std and an instance of SQL 2012 Std. Each have a Max RAM Cap (128GB for 2014, 60GB for 2012) so that the server usually runs with about 10GB free RAM. Yesterday, the 2014 instance produced error message "Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION". I then drop the Max RAM Cap of the 2012 instance to 50GB so that there are more free RAM for additional usage. However, same thing happens again today. The error occurred a couple of times during one hour. The queries are still possible to run during the period, but with very slow performance. The following is a dump from the error log. Can anyone help to point out what could be root cause of the problem? What I can see is that there are still 19GB free RAM.
2017-08-02 05:01:14.61 spid137 Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 32
2017-08-02 05:01:14.61 spid137
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 19326009344
Available Virtual Memory 8370134982656
Available Paging File 238982365184
Working Set 134853488640
Percent of Committed Memory in WS 100
Page Faults 132871064
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
2017-08-02 05:01:14.61 spid137
Memory Manager KB
---------------------------------------- ----------
VM Reserved 415244212
VM Committed 131072000
Locked Pages Allocated 0
Large Pages Allocated 0
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 131072000
Current Committed 131072000
Pages Allocated 121892000
Pages Reserved 80040
Pages Free 49216
Pages In Use 20728256
Page Alloc Potential 103790144
NUMA Growth Phase 2
Last OOM Factor 2
Last OS Error 0
2017-08-02 05:01:14.61 spid137
Memory node Id = 0 KB
---------------------------------------- ----------
VM Reserved 415202788
VM Committed 65535992
Locked Pages Allocated 0
Pages Allocated 59623792
Pages Free 11928
Target Committed 65535992
Current Committed 65535992
Foreign Committed 364980
Away Committed 0
Taken Away Committed 0
2017-08-02 05:01:14.61 spid137
Memory node Id = 1 KB
---------------------------------------- ----------
VM Reserved 41360
VM Committed 65535988
Locked Pages Allocated 0
Pages Allocated 62268208
Pages Free 37288
Target Committed 65535992
Current Committed 65535992
Foreign Committed 2560696
Away Committed 0
Taken Away Committed 0
2017-08-02 05:01:14.61 spid137
Memory node Id = 64 KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
2017-08-02 05:01:14.61 spid137
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 69648
2017-08-02 05:01:14.61 spid137
MEMORYCLERK_SQLGENERAL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 208
2017-08-02 05:01:14.61 spid137
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 69856
2017-08-02 05:01:14.61 spid137
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 13458756
VM Committed 5655728
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1308216
2017-08-02 05:01:14.62 spid137
MEMORYCLERK_SQLBUFFERPOOL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 109068208
2017-08-02 05:01:14.62 spid137
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------- ----------
VM Reserved 13458756
VM Committed 5655728
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 110376424
2017-08-02 05:01:14.62 spid137
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2856
2017-08-02 05:01:14.62 spid137
MEMORYCLERK_SQLQUERYEXEC (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-08-02 05:01:14.62 spid137
MEMORYCLERK_SQLQUERYEXEC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2864
2017-08-02 05:01:14.63 spid137
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2296
2017-08-02 05:01:14.63 spid137
MEMORYCLERK_QUERYDISKSTORE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 592
2017-08-02 05:01:14.63 spid137
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
2017-08-02 05:01:14.63 spid137
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- ----------
VM Reserved 32768
VM Committed 32768
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 13896
2017-08-02 05:01:14.64 spid137
MEMORYCLERK_SQLSTORENG (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 9928
2017-08-02 05:01:14.64 spid137
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------- ----------
VM Reserved 32768
VM Committed 32768
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 23824
2017-08-02 05:01:14.64 spid137
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1728
2017-08-02 05:01:14.64 spid137
MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 872
2017-08-02 05:01:14.64 spid137
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2600
2017-08-02 05:01:14.64 spid137
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- ----------
VM Reserved 6304512
VM Committed 1298252
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 43944
2017-08-02 05:01:14.65 spid137
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 560
2017-08-02 05:01:14.65 spid137
MEMORYCLERK_SQLSERVICEBROKER (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-08-02 05:01:14.65 spid137
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 576
2017-08-02 05:01:14.65 spid137
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2017-08-02 05:01:14.66 spid137
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 88
2017-08-02 05:01:14.66 spid137
MEMORYCLERK_SNI (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 72
2017-08-02 05:01:14.66 spid137
MEMORYCLERK_SNI (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-08-02 05:01:14.66 spid137
MEMORYCLERK_SNI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 176
2017-08-02 05:01:14.67 spid137
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 296
2017-08-02 05:01:14.67 spid137
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-08-02 05:01:14.67 spid137
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-08-02 05:01:14.68 spid137
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 85640
2017-08-02 05:01:14.68 spid137
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 5888
VM Committed 5888
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2017-08-02 05:01:14.68 spid137
MEMORYCLERK_XE_BUFFER (node 1) KB
---------------------------------------- ----------
VM Reserved 2944
VM Committed 2944
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2017-08-02 05:01:14.68 spid137
MEMORYCLERK_XE_BUFFER (Total) KB
---------------------------------------- ----------
VM Reserved 8832
VM Committed 8832
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2017-08-02 05:01:14.69 spid137
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 352
2017-08-02 05:01:14.70 spid137
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 92368
2017-08-02 05:01:14.70 spid137
MEMORYCLERK_SOSNODE (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 60344
2017-08-02 05:01:14.70 spid137
MEMORYCLERK_SOSNODE (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2496
2017-08-02 05:01:14.70 spid137
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 155208
2017-08-02 05:01:14.70 spid137
MEMORYCLERK_SOSOS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2017-08-02 05:01:14.70 spid137
MEMORYCLERK_SOSMEMMANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 1599976
VM Committed 1599408
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2017-08-02 05:01:14.70 spid137
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 384
SM Committed 384
Pages Allocated 0
2017-08-02 05:01:14.71 spid137
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 200
2017-08-02 05:01:14.72 spid137
MEMORYCLERK_FILETABLE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2017-08-02 05:01:14.73 spid137
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 5096
2017-08-02 05:01:14.76 spid137
MEMORYCLERK_SQLLOGPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8376
2017-08-02 05:01:14.76 spid137
MEMORYCLERK_LWC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
2017-08-02 05:01:14.76 spid137
MEMORYCLERK_FSCHUNKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48
2017-08-02 05:01:14.77 spid137
==========================================================
Output of queries
physical_memory_in_use_kb large_page_allocations_kb locked_page_allocations_kb total_virtual_address_space_kb virtual_address_space_reserved_kb virtual_address_space_committed_kb virtual_address_space_available_kb page_fault_count memory_utilization_percentage available_commit_limit_kb process_physical_memory_low process_virtual_memory_low
131744636 0 0 8589934464 416300984 132589644 8173633480 139058025 100 232741340 1 0
cpu_ticks ms_ticks cpu_count hyperthread_ratio physical_memory_kb virtual_memory_kb committed_kb committed_target_kb visible_target_kb stack_size_in_bytes os_quantum os_error_mode os_priority_class max_workers_count scheduler_count scheduler_total_count deadlock_monitor_serial_number sqlserver_start_time_ms_ticks sqlserver_start_time affinity_type affinity_type_desc process_kernel_time_ms process_user_time_ms time_source time_source_desc virtual_machine_type virtual_machine_type_desc
32194551409313872 9755807460 16 8 217912092 8589934464 131072000 131072000 131072000 2093056 4 5 32 704 16 26 261943637 1204684701 2017-04-26 15:05:35.517 2 AUTO 862978889 24574153231 0 QUERY_PERFORMANCE_COUNTER 0 NONE
FlushCache: cleaned up 36988 bufs with 1477 writes in 87914 ms (avoided 1054 new dirty bufs) for db 6:0 – Stock 2001 Aug 03 '17 at 06:17
select * from sys.dm_os_process_memoryandselect * from sys.dm_os_sys_info. Please add the output in the question. Apart from the 2 instances is their anything else running on Server, like any TP application?. When you are getting OOM error did you found out any specific query/job running at that time. – Shanky Aug 03 '17 at 08:01FlushCachemessage read this. https://dba.stackexchange.com/questions/74292/flushcache-messages-appearing-in-log-at-specific-times – SqlWorldWide Aug 03 '17 at 13:21process_physical_memory_low= 1 this indicates that SQL Server physical RAM is low. What is total size of the database ? Are you using SSIS to do this task – Shanky Aug 03 '17 at 17:29