Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
What are your min server memory and max server memory settings? Once SQL Server reserves the minimum you specify, it will never release it unless you change the setting or bounce the instance. 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. –  Nick Chammas Oct 11 '11 at 13:41
    
@Nick: min server memory: 0 (MB); max server memory: 600 (MB). –  jrara Oct 12 '11 at 6:13

3 Answers 3

up vote 10 down vote accepted

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.

share|improve this answer
2  
Per Microsoft: "Under Windows Server 2003, SQL Server uses the memory notification API QueryMemoryResourceNotification to determine when the buffer pool may allocate memory and release memory." So if you're just worried about starving other apps that may need the memory more, SQL Server in theory will release memory it doesn't need when Windows tells it to. The only reason you should need to force a release by restarting the instance is if this mechanism is not working as designed or your min/max memory settings are incorrect. –  Nick Chammas Oct 11 '11 at 18:17
    
Another useful msdn article to clarify on this msdn.microsoft.com/en-us/library/ms178145.aspx. As long as SQL Server has reached it's min server memory setting it will release memory in response to low memory notifications from the OS. In my experience it has to be quite extreme for this to happen though. –  georgeb Oct 11 '11 at 19:01
    
Precisely @georgeb_dba, it will not readily give it up. Ergo, stop/start and get back your laptops limited memory immediately. –  Mark Storey-Smith Oct 11 '11 at 19:08
DBCC FREESYSTEMCACHE ('ALL') 
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE 

will free up the pool .

share|improve this answer

It will give up the memory it is not using automatically as other apps try to commit it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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