I am using SQL 2008R2 (prod server). Total server memory is 20 gb. I assigned for sql 14 GB and setting max and min limit in SQL memory settings. Currently the sql utilizes total memory( No active transactions) it did not release the memory. When i am trying to insert data i am getting error due to this.
NOte : Buffer cache hit ratio is 100%
How to release the memory. After restarting the SQL server it releases memory after 7days i am facing same issue.
same configuration used in QA but SQL is not using much memory in QA environment. What is the different between those prod and QA?
Please help us.
asked Oct 06, 2015 at 12:18 PM in Default
What did you set min and max server memory to? What error message are you getting? Is it definitely SQL that's using all the memory? Have you looked in the server's Task Manager?
answered Oct 06, 2015 at 01:16 PM
set the max memory to 5 GB and the move it again to 14 GB. It should release the memory but eventually SQL will grow up to the max specified.
answered Oct 11, 2015 at 04:11 AM
If you really want to release memory, it isn't necessary to restart SQL Server service.
Just run a DBCC dropcleanbuffers.
I'd also recommend to check with the DMVs what else is consuming memory. In 2008 (R2) the max memory setting only is valid for the buffer pool, not for the whole service. Maybe have a problem with too many single used plans. Hint: active the option Optimize for ad hoc workloads.
answered Oct 11, 2015 at 08:53 AM