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???

  • 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

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.

  • 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.

  • 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 – MartW Nov 12 '09 at 0:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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