asked
SQL server not releasing memory
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.
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?
Yes , I checked in Task manager. It shows SQL consumes full memory.
So the memory has all been used by sqlserver.exe? And could you answer my other questions, please?
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. cheers
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.
20 People are following this question.
