One of the servers I have been performance monitoring started throwing the following warnings from the Resource-Exhaustion-Detector:
Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: sqlservr.exe (1560) consumed 14960812032 bytes, ReportingServicesService.exe (1936) consumed 506359808 bytes, and w3wp.exe (7376) consumed 273764352 bytes.
SystemCommitLimit 38068215808 SystemCommitCharge 37800669184 ProcessCommitCharge 16727490560 PagedPoolUsage 359088128 PhysicalMemorySize 17098584064 PhysicalMemoryUsage 16881131520 NonPagedPoolUsage 221425664 Processes 48
This server is windows server 2008, running MSSQL 2008 R2, has 16GB of RAM, and 24 processors. It runs SQL, and a web service that accesses SQL for Data.
The numbers that I included in the quote are from the details section of the event viewer. I have not been able to identify a root cause. I already know SQL needs a lot of memory to function, and it was using a lot of memory at the time, but I also had the cap set to 14000MB.
SQL began getting the Out of Memory error in addition to the Resource-Exhaustion-Detector warnings.
What would the best approach to finding the root cause of this be? I haven't seen anything that looks out of the ordinary in the logs. After a few hours of this error repeating over and over, the memory finally ran out and services started to fail until the service had to restart.
Isn't SQL smart enough to relinquish some of it's memory when there is pressure? The Page File (Virtual Memory) was 20GB, and SQL was only using 16GB of physical memory. What was filling up the rest of the Virtual Memory? Was SQL actually using all of that page file?
Should I be looking for a memory leak? Log file growth?
The .mdf used the most on the server grows about 100mb every day. The Log file has been growing by 3gb at a time as is now 40gb.
Usually when there is memory pressure, we've never gotten to the point where the server just crashes. It usually just runs painfully slow until the pressure is gone.
Is there a way to effectively stop this issue from occurring?