7

On my dev box I have my SQL Server configured with a high maximum amount of memory (8GB of 16GB). This is required for many development tasks.

Sometimes I want my memory back, though. I don't want to leave 8GB consumed permanently.

What's a good way to get SQL Server to suddenly release all it's memory to the operating system?

DBCC DROPCLEANBUFFERS does not do that. It merely marks clean buffers as available.

Restarting SQL Server is a problem because this causes a 1 minute database recovery to be run due to this bug/feature: Stopping the SQL Server service causes databases to not shut down cleanly (please vote for that connect item; seems like an egregious bug).

  • Have you considered changing max server memory when you're done doing what you're doing with it? You could put this in a job or a procedure so that you don't have to do it manually. Also, don't be too Chicken Little about restarts, I restart SQL Server all the time, but I don't use the Control Panel applet, I use SQL Server Configuration Manager. This is a development box, right, not a production machine? Even if recovery takes a whole minute when you restart the service, is it really a problem? Finally, the bug seems relevant only if you're actively using the database when you shut down... – Aaron Bertrand Sep 16 '15 at 17:03
  • ...which is something you can probably avoid, simply by shutting down SSMS and/or your apps before restarting SQL Server. – Aaron Bertrand Sep 16 '15 at 17:07
  • (Bug fixed). The problem with the 1min recovery is that SQL Server likes to overwhelm the disk which basically pauses anything that I do with it. Shutting down apps is cumbersome. There's SSMS which is notorious for not letting connections go, and also ASP.NET which has a connection pool. – boot4life Sep 16 '15 at 17:13
  • I'll try reducing the max memory setting. This has had bad consequences in the past for me. There seem to be SQL Server bugs associated with it. I had performance problems after reducing and then increasing the max server memory variable. I also had queries waiting for memory grants indefinitely. I also had minutes of 1 core at 100% CPU. SQL Server does not like its memory reduced I think. This seems to be poorly tested by the dev team. – boot4life Sep 16 '15 at 17:15
16

I routinely increase and decrease the max server memory configuration option for the SQL Server 2005-2016 instances running on my laptop (with lock pages in memory enabled). Has never caused me any problems, and memory always seems to be released quickly. I also stop, start, and restart instances as I find I need them (using SQL Server Configuration Manager, which I keep open).

As far as checkpoint is concerned, how you configure that depends on version. Manual checkpoint with a duration target has been available since 2005 (though it is undeniably more effective in later versions).

Running a manual checkpoint <duration> per database before shutdown will help spread the I/O load and reduce recovery time on startup. Managed checkpointing will also reduce I/O needed when a memory shrink is requested because there will be fewer dirty pages to write to persistent storage. There is no particular benefit to dropping clean buffers before reducing the max memory setting that I have noticed.

Not using 'lock pages in memory' may increase the rate at which SQL Server can release memory, but I rely on that to avoid paging SQL Server memory to disk. If you don't need it for that purpose, you might choose to run without it.

| improve this answer | |
5

Sometimes I want my memory back, though. I don't want to leave 8GB consumed permanently.

What's a good way to get SQL Server to suddenly release all it's memory to the operating system?

DBCC DROPCLEANBUFFERS does not do that. It merely marks clean buffers as available.

Since you said you want to free memory on DEV machine so you can use below query

DBCC FREESYSTEMCACHE ('ALL') 
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

But note the above commands won't cause SQL Server process to release memory committed it would just clear caches. That is what you are experiencing.

After running above query you can change SQL Server max server memory to lower value( may be 4-5G in your case) this WILL force SQL Server processes to release memory and thus bring down memory consumption again I am suggesting this as machine in picture is DEV machine. Changing max server memory does not requires restart. The other thing is restarting SQL Server service this will definitely clear caches and memory held by SQL Server process would be released. But after you restart SQL Server, it might, after some time take back all memory.

| improve this answer | |
-3

Stop the SQL Server (MSSQLSERVER) service and restart it. Does the trick.

| improve this answer | |
  • 2
    Read the question a bit more carefully. They don't want to restart the server. "Restarting SQL Server is a problem because this causes a 1 minute database recovery to be run" – Erik Darling Sep 3 '18 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.