SQL Server Memory Errors – How to Fix Multiple Memory Errors in Error Log

sql serversql-server-2017sql-server-express

MSSQL-Server 2017 Express Edition runs on my Linux server.
The server has 4GB RAM.
The total size of DB is 300MB.

My website and the sqlserver are on the same server, it's a small website with max of 400-300 visitors a day.
Memory usage in linux looks fine, mssql takes only 20% memory:

enter image description here

Some additional sql memory information:
enter image description here

Maximum server memory is set to 2147483647 which is the default I guess.

The errors I get:

enter image description here
enter image description here

After restarting the mssql-service there are no problems for something like 2 days and then it repeats it self.

I would happily get an answer that will solve this for me, but I would also like to get a resource where I can learn about memory allocation/cache/pages/ related to sql server because I am not a sql expert, I am a programmer.

Best Answer

You're showing some conflicting information. 20% memory utilisation on a 4 GB server would be around 820 MB, however, your screenshots indicate somewhere between 3.8 and 4 GB is consumed by SQL Server at the moment.

Those errors are clearly indicating that SQL Server has insufficient memory available to process a request. While you could, and should, set the max server memory setting to something other than 2147483647, this won't change the fact that SQL Server wants additional memory.

You need to look at optimising the server and workload, apply best practises to the instance to ensure it is running optimally, tune your SQL code to make sure it is not using excessive memory, and you will possibly need to allocate additional memory to the server.

After restarting the mssql-service there are no problems for something like 2 days and then it repeats it self.

This is because SQL Server flushes all of its caches upon restart, and it takes a while to fill that up again, given the low utilisation you've indicated. Setting a max server memory setting between 2 and 3 GB should help alleviate some of these errors because SQL Server will manage memory differently than if the setting is configured to simply consume as much memory as possible, however, it is likely that you will eventually hit these errors again as 4 GB for a Web & SQL Server is quite low.

Related Question