0

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.

Improve this question
4
  • What else have you got running on the machine? 4Gb RAM isn't much these days for a web & DB server
    – Philᵀᴹ
    Commented Oct 20, 2019 at 14:27
  • 4
    Error 17300's message reads: SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
    – David Browne - Microsoft
    Commented Oct 20, 2019 at 15:58
  • @Philᵀᴹ it's a linux machine thus no GUI RAM is used for op system at all, the website used to work fine with mysql db when the server had only 2GB of RAM, I doubled it and replaced mysql with mssql and I am getting errors, I am sure it's something related to some preferences that I am not aware of.
    – Offir
    Commented Oct 21, 2019 at 15:32
  • @DavidBrowne-Microsoft In order to ensure there is enough free physical memory for the Linux Operating System, the SQL Server process uses only 80% of the physical RAM by default. For some systems which large amount of physical RAM, 20% might be a significant number. learn.microsoft.com/en-us/sql/linux/… This error is very general and not informative, why it ran out of memory if it says it saves 20% for OS, and why it crashes and not restart? Max connections set to 600 which is more than enough by far!
    – Offir
    Commented Oct 22, 2019 at 9:53

1 Answer 1

Reset to default
3

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.

Improve this answer
1
  • Well, this is exactly what I was hoping for as an answer. After reading the content from the links you have provided and many other recommendation online, I have 2 conclusions: Microsoft documentation isn't helpful and frustrating, you begin to read 1 document and get multiple references that has multiple references until you forget what you were looking for from the first place.. I changed the max memeory to 3000MB, hopefully this will help even though they write that by default sql uses max of 80% memory and keep 20% to OS. I hope this will help.
    – Offir
    Commented Oct 21, 2019 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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