1

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

noob2487
  • 115
  • 1
  • 4

2 Answers2

8

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.

Massimo
  • 70,200
  • 57
  • 200
  • 323
  • 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
1

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.

MartW
  • 1,305
  • 10
  • 15
  • 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 00:12