We have a Windows Server 2008R2 server with 32GB or RAM running SQL Server 2008R2, along with an IIS server. The machine is also used as a backup server.
The problem is, periodically (every week or two) the SQL takes up all the memory, literally all of it: resmon shows 0MB available. Now, I did look the problem up, and I know SQL is supposed to take up the entire memory, but it really hurts the web performance, and in the end the SQL itself starts stuttering; scheduled jobs don't execute (the logs say "insufficient resources") etc.
I tried limiting the max memory size to 4096MB. The memory footprint dropped - and rose again to full in a week. I tried scheduling a job that would empty the caches every night. Is that likely to help? If not, what will?
edit Last time it crashed, already after I limited it to 4GB, I wasn't on-site, unfortunately. But the SQL Server logs show the available page file space dropping below 4MB, and the Windows logs report low virtual memory conditions for hours, with sqlservr.exe consuming the most memory (though only about 1,640,919,040 bytes, for some reason).