SQL server not releasing memory


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.

more ▼

asked Oct 06, 2015 at 12:18 PM in Default

avatar image

1 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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?

more ▼

answered Oct 06, 2015 at 01:16 PM

avatar image

David Wimbush
10.7k 31 34 43

Yes , I checked in Task manager. It shows SQL consumes full memory.

Oct 06, 2015 at 01:47 PM mssql.sree

So the memory has all been used by sqlserver.exe? And could you answer my other questions, please?

Oct 06, 2015 at 02:27 PM David Wimbush
(comments are locked)
10|1200 characters needed characters left

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.


more ▼

answered Oct 11, 2015 at 04:11 AM

avatar image

1 1

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 11, 2015 at 08:53 AM

avatar image

1.3k 15 19 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 06, 2015 at 12:18 PM

Seen: 100 times

Last Updated: Oct 11, 2015 at 08:53 AM

Copyright 2018 Redgate Software. Privacy Policy