Skip to main content
added 2 characters in body
Source Link
Shanky
  • 19.1k
  • 4
  • 36
  • 58

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.

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.

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.

Source Link
Shanky
  • 19.1k
  • 4
  • 36
  • 58

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.