1

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
  • 1
    Are you running the instances on physical machine or VMware ? – Shanky Aug 2 '17 at 19:52
  • How much physical memory is in the server? If it's a VM, how much RAM is allocated to the VM? – Max Vernon Aug 2 '17 at 21:04
  • Thanks Shanky! It is a physical machine. 214GB RAM in total. Today, I turned on performance monitor, logged every 5 min. the available RAM is constantly at 18GB. However, we still get the same error around similar time (4-5am). In addition, I also get this error today, which happens about 2 hours before FlushCache: cleaned up 36988 bufs with 1477 writes in 87914 ms (avoided 1054 new dirty bufs) for db 6:0 – Stock 2001 Aug 3 '17 at 6:17
  • What is output of select * from sys.dm_os_process_memory and select * 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 3 '17 at 8:01
  • 1
    @Stock2001 I can see process_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 3 '17 at 17:29

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.