Hi, 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. Thanks, Sree.
If you really want to release memory, it isn't necessary to restart SQL Server service. Just run a DBCC dropcleanbuffers. SQL Server will release memory since it unloads the cached data pages. 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.