question

mssql.sree avatar image
mssql.sree 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.
dbamssqlmemory-utilizationsql memory consumptionout-of-memory
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
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?
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes , I checked in Task manager. It shows SQL consumes full memory.
0 Likes 0 ·
So the memory has all been used by sqlserver.exe? And could you answer my other questions, please?
0 Likes 0 ·
chawiro avatar image
chawiro answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DirkHondong avatar image
DirkHondong answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.