How do you force SQL Server to release memory?

The modified script below worked for me. I needed to temporarily release a bunch of RAM held by SQLServer so that we could run some other one-off processes on the same server. It temporarily releases SQL's reserved mem space while still allowing it to gobble the mem back up as needed.

I added a built-in wait to let SQLServer actually release the mem before bumping it back to the original level. Obviously adjust the values as needed to suit your needs.

sp_configure 'show advanced options', 1;  GO  RECONFIGURE;  GO  /*** Drop the max down to 64GB temporarily ***/sp_configure 'max server memory', 65536;  --64GBGO  RECONFIGURE;  GO  /**** Wait a couple minutes to let SQLServer to naturally release the RAM..... ****/WAITFOR DELAY '00:02:00'; GO/** now bump it back up to "lots of RAM"! ****/sp_configure 'max server memory', 215040;    --210 GBGO  RECONFIGURE;    GO  

SQL Server always assumes it is the primary application running. It is not designed to share resources. It will always take all the available memory and it will only release it for the operating system unless you throttle with 'max server memory'.

By design, Sql Server does not play well with others.

This sqlskills article recommends a baseline for throttling followed by monitoring and raising the throttle as needed:

https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

I don't have a solution for how to release the allocated memory. However, for our purposes we were able to figure out, how to allow active-active clusters to run safely. We've decided to set minimum server memory to ~2GB. This is helpful because no matter how much max memory an instance decides to use, it will never run other instances out of memory. So again, this solves our purpose but it still doesn't answer the question of how much memory is actually being used, how low can we drop the max server memory, etc...