Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am using SQL Server 2005.

I am running a job which processes around 100 K records. Job runs fine, it takes are 45 mins to execute, which is good.

But after that job is processed, I can see instance of SQL Server 2005 still there with around 900 MB of Memory. I waited for around 2 hrs but that memory was not released.

Is there any process which takes care of memory here, something like GC (unpredictable) Or am I doing something wrong???

share|improve this question
    
When you have a database table with 20 million heavilly read records, this non-releasing of memory is a good thing –  Mark Henderson Nov 11 '09 at 22:42
    
Thanks for all the responses guys. Sorry my reputation is too low to upvote anyone. –  noob2487 Nov 11 '09 at 23:29

2 Answers 2

up vote 8 down vote accepted

SQL Server doesn't release buffer memory unless the O.S. actively reclaims it; so this is expected behaviour.

If there is a memory shortage (f.e. some other application on the system needs some which is not available), SQL Server will release unused memory.

share|improve this answer
    
Thanks Massimo. I am unable to check whether SQL Server is releasing memory or not. Is there any reference which talks about same thing? I am lil afraid to move scripts to staging from dev, after seeing so much memory usage. Hope you understand. –  noob2487 Nov 11 '09 at 22:57
    
This is correct, don't feel like digging for documentation, but rest assured, it is the truth. You must get pressure from another application on the server for it to release. –  Sam Nov 12 '09 at 16:21

SQL server doesn't work like that. It'll release the memory if something else needs it, but otherwise it'll keep hold of what it has to help it run more efficiently. It is normal for a server running SQL Server to appear to have little free memory.

share|improve this answer
    
Right now I am running it on my local machine (SQL Express, XP). All the applications have become slow on my machine. Is there a way I can force SQL Server to release the memory? (other than killing the process, ofcourse) –  noob2487 Nov 11 '09 at 23:22
1  
How much memory does your local machine have? You can tel SQL Express to not use more than a certain amount using some SQL : use master Go exec sp_configure 'show advanced options', 1; Go RECONFIGURE; GO exec sp_configure 'max server memory (MB)', 700; GO RECONFIGURE; GO –  CodeByMoonlight Nov 12 '09 at 0:12

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.