0

We have an Azure Linux VM with a local MSSQL server. I recently had to reboot the VM, and since then I haven't been able to start the MSSQL server. The error logs would say Recovery of database 'MyDB' (6) is 1% complete ..., then hang the VM to the point that I could not access it over SSH, and eventually the mssql-server service would just stop. The last time this happened there was finally an error message in the logs:

2017-07-30 10:20:35.19 spid24s      Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 8
2017-07-30 10:20:38.85 spid24s     
Process/System Counts                         Value
---------------------------------------- ----------
Available Physical Memory                5837422592
Available Virtual Memory                 140733193256960
Available Paging File                    5837422592
Working Set                              4294967296
Percent of Committed Memory in WS                82
Page Faults                                       0
System physical memory high                       1
System physical memory low                        0
Process physical memory low                       0
Process virtual memory low                        1
2017-07-30 10:21:22.83 spid24s     
Memory Manager                                   KB
---------------------------------------- ----------
VM Reserved                                 5883372
VM Committed                                5091232
Locked Pages Allocated                            0
Large Pages Allocated                             0
Emergency Memory                               1024
Emergency Memory In Use                           8
Target Committed                            5700608
Current Committed                           5091232
Pages Allocated                             2923720
Pages Reserved                                    0
Pages Free                                  2024712
Pages In Use                                 217880
Page Alloc Potential                        5197704
NUMA Growth Phase                                 0
Last OOM Factor                                   9
Last OS Error                                     0
2017-07-30 10:21:23.78 Server      Long Sync IO: Scheduler 0 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms
2017-07-30 10:22:40.64 spid24s     
Memory node Id = 0                               KB
---------------------------------------- ----------
VM Reserved                                 5883244
VM Committed                                5091196
Locked Pages Allocated                            0
Pages Allocated                             2923752
Pages Free                                  2024680
Target Committed                            5700568
Current Committed                           5091200
Foreign Committed                                 0
Away Committed                                    0
Taken Away Committed                              0
2017-07-30 10:23:21.93 spid24s     
Memory node Id = 64                              KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                     36
Locked Pages Allocated                            0

The VM is running Ubuntu with 8GB ram. Do I need more memory, or are there other solutions?

  • How big is the database? Is anything other than SQL Server running on the VM? Can you try enabling Lock Pages In Memory? – Erik Darling Jul 30 '17 at 13:51
  • @sp_BlitzErik (1) I’m not sure, but I think around 15-20GB. (2) No. (3) What’s the best way to do that on Linux? – Benjy Wiener Jul 30 '17 at 14:09
  • What is the azure compute vm size and how many disks are attached? If you're getting errors about long IOs, then I would investigate IO. – Sean Gallardy - Retired User Jul 30 '17 at 18:18
-2

The error message is indicating internal memory pressure; could you please share the complete errorlog output when the OOM condition had occurred. We would like to validate memory consumption by each clerk.

How large is your database which is still in recovery; It looks like there some long outstanding i/o operations occurring. Are there any latencies at the storage layer?

-Pradeep

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.