Question with memory usage of MSDE

[johnsun1976]

Hi, we're noticing that the memory usage grows.. It once went up to 112 MB.. It usually goes back down to 14-20 when we stop and start the service.. Can any explain why MSDE is using so much memory and why it's not being deallocated?
Thanks
John

 

[tlbroadbent]

SQL Server memory usage increases as data is read and loaded into cache. MSDE does the same. As databases are opened and data accessed, more data pages will be loaded into cache.

There is an option that you can set so that SQL Server or MSDE closes databases when not in use. This should reduce memory usage. The default is ON for MSDE so this may not solve your problem.

The following excerpt is from SQL BOL.

AUTO_CLOSE

When set to ON, the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete, thereby freeing any resources. By default, this option is set to ON for all databases when using Microsoft® SQL Server™ 2000 Desktop Engine, and OFF for all other editions, regardless of operating system.

Usage: ALTER DATABASE DBName SET AUTO_CLOSE ON

NOTE: This is not a good option to set ON for databases that have a lot of activity because opening and closing databases will slow the processes. It is usually OK for MSDE.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.