I just a bit of an issue there where a SQL Server database was eating up quite a lot of RAM for its size.
A bit of background:
The database is for a small content managed website. The CMS is custom built in ASP which a bit of unoptimised queries littered throughout, but nothing too bad. The actual database takes up 132MB on disk.
Now back to SQL Server.
When looking through the server options I came across the Memory section. I noticed it was set to 2147483647MB which got me thinking that setting this value to something smaller could be a way to deal with the issue.
I set it to 500MB, which is quite a big difference.
So far the website is running great and there has been no more issues with memory on the server.
How can I determine what is the best value to set this to? Is 500MB to low? There is going to be another database on this server. It will be much smaller though, about 50MB.
EDIT (More Information)
I should add that the issue that caused me to seek out this memory setting was that of SQL Server not releasing memory back to the OS when it needed it. I run an old site that requires pages to be compiled on the fly (i.e. doesn't have a bin folder) and there was an issue with some pages not being compiled when needed. There was a server out of memory error.
This particular server has 2GB of RAM.