4

Developer in DBA role here, we recently migrated from 2008 to 2019 and are having memory related problems on the new server. Every 3 to 6 days the OS will start paging and CPU usage would spike to 99%, the server would be totally unresponsive. The server is running on VMWare with 36Gb memory. After the first crash the min max memory on sql server was set to 10gb and 30gb (leaving 6gb for the OS). LPIM is enabled and the full allocation memory is reserved in VMWare and as far as I can see no ballooning happened VMWare side.

I've noticed in the logs the problem always follows the following pattern:

  1. Backup on one of the biggest DB fails with the error: 1453(Insufficient quota to complete the requested service.)

  2. All subsequent backups fail with: BackupIoRequest::ReportIoError: write failure on backup device '4aa04866-4040-4be1-8453-d7da0116efe5_2'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

  3. Failed allocate pages: FAIL_PAGE_ALLOCATION 1025

  4. Bunch of Memory related messages. some examples:

    Memory Manager                                   KB
    ---------------------------------------- ----------
     VM Reserved                                62168828
     VM Committed                                 377644
     Locked Pages Allocated                     25468848
     Large Pages Allocated                        264192
     Emergency Memory                               1024
     Emergency Memory In Use                          16
     Target Committed                           27262976
     Current Committed                          25846496
     Pages Allocated                            24510040
     Pages Reserved                               332032
     Pages Free                                   397904
     Pages In Use                                5739552
     Page Alloc Potential                       20160280
     NUMA Growth Phase                                 0
     Last OOM Factor                                   2
     Last OS Error                                     0
    

And then

Message
MEMORYCLERK_XE_BUFFER (node 0)                   KB
---------------------------------------- ----------
VM Reserved                                   12672
VM Committed                                  12672
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                   0

Backups are done through Commvault and are administrated by a different department in the org. I've noticed that when backups are taken, the number of dump devices are set to 10(old server it was set to 5), is that normal? Could it be the backups that are causing the memory/disk space problems or are the backups failing because of some other memory pressure caused by something else? If anyone can point me in the right direction it will be greatly appreciated, thank you.

Edit 1: Log dump here Current memorystatus here. I also like to note, that all the migrated databases is still in 2008 compatibility mode. I've also disabled query store on all.

7
  • Can you post output of dbcc memorystatus generated after OOM error in the question. Since O/P is big it would be better to add it on shared drive and post link in the question. Also add the output of sp_readerrorlog. Please mask the sensitive data.
    – Shanky
    Jun 5 '20 at 8:05
  • Windows error 1453 means you've used up your commit limit (RAM + Page File). Your SQL Server commit charge looks fine (locked pages allocated + vm committed) only 26Gb. Do you use any Linked Servers? Is SQL Servers process's committed virtual memory significantly higher than 300MB? Does restarting SQL Server resolve the issue, or do you need a reboot? Jun 6 '20 at 22:12
  • @Shanky I edited the post with links to the current MemoryStatus and a extract of the log when the incident occured. Thanks.
    – diffie
    Jun 8 '20 at 16:54
  • 1
    Then you don't know what SQL Server's process memory is at that time. You need to discover what's using all the virtual memory when this occurs. Might be the linked server OleDb providers in the SQL Server process leaking memory. Might be some other process. What kinds of linked servers? I would start a Performance Monitor collector recording the "Page File Bytes" "Private Bytes" and "Working Set" for all processes. Jun 8 '20 at 17:40
  • 1
    Is Commvault doing application aware backups of the databases? Could be the database log files are massive and it fails when it tries to back them up.
    – Jhunter1
    Jun 10 '20 at 14:03

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.