snippetsqlModerate
How to free SQL Server memory?
Viewed 0 times
freesqlmemoryhowserver
Problem
I have a SQL Server sandbox installation on my local computer. I have set the memory SQL Server instance can use to 1000 mb. When running an intensive operation, memory usage rises up to 1000 mb. When operation is over, SQL Server is still holding the memory. How to free this memory reservation?
Solution
Stop/start the service, nothing else will release the memory back to the OS.
Obviously not something you'd ever want to do with an operational server but perfectly reasonable for a local sandbox. With 3 different instances on my laptop, its the only viable way.
Edit following @Nick's comment.
As James noted, if it reserves more than the minimum you specify, it
should in theory release it when it doesn't need it and there is
memory pressure on the machine.
What's your definition of "doesn't need it"? Once the buffer pool has been filled, it isn't going to drop pages out until they become disfavoured by other pages being brought in. If you want the memory back immediately, as is the case for @jrara, you've got to stop/start.
Obviously not something you'd ever want to do with an operational server but perfectly reasonable for a local sandbox. With 3 different instances on my laptop, its the only viable way.
Edit following @Nick's comment.
As James noted, if it reserves more than the minimum you specify, it
should in theory release it when it doesn't need it and there is
memory pressure on the machine.
What's your definition of "doesn't need it"? Once the buffer pool has been filled, it isn't going to drop pages out until they become disfavoured by other pages being brought in. If you want the memory back immediately, as is the case for @jrara, you've got to stop/start.
Context
StackExchange Database Administrators Q#6691, answer score: 13
Revisions (0)
No revisions yet.