Sample Header Ad - 728x90

Why is my SQL Server giving out of memory errors when there should be plenty?

4 votes
1 answer
7420 views
I have a production system running SQL Server 2019 Standard edition. It recently had a problem 3 times in 1 day where it became unresponsive until a reboot. Errors seem to point to memory limitations. 32GB is installed and the machine is dedicated to MSSQL. Max Memory set to 26GB. (edited 2020-11-23 to remove some erroneous info re: MEMORYCLERK_SQLGENERAL.) The most damnning info from the logs is a series of FAIL_PAGE_ALLOCATION errors. Note: Since having the problem I've bumped up to a larger instance size (64GB) **Update 2020-11-22**: The problem has reoccurred on the new larger instance. The memory errors began a few minutes after a scheduled backup of several databases. That may or may not be coincidence. It became nonresponsive and I forcibly restarted it. - I will start the MSFT support process as suggested by Josh and sqL_handLe. - I've made an Agent job (sproc here ) to collect the results from dm_os_memory_objects every 5 minutes, based on sqL_handLe's query. - I've shared recent logs here . (I replaced the nl tags with actual newlines to make it easier to read.) - Disabled SSAS as suggested by Jana Sattainathan. I did confirm it was running and had 13GB "Min Working Set" as shown in the linked article, even though its actual working set was small. I think it's possible this subtle issue is the culprit, but if it's as dangerous as it seems, I'm very surprised I haven't run into it before. Jana, consider converting your comment to an answer? The non-SQL load on this server is near-zero. The backup agent is just Ola Hallengren's TSQL scripts. The default SQL services are running but not in use. As mentioned, I've stopped SSAS. We occasionally use SSIS but not since the instance upgrade.
Asked by solublefish (143 rep)
Nov 19, 2020, 01:29 AM
Last activity: Nov 23, 2020, 03:53 PM