| Author |
Topic |
|
hiuhiu
Starting Member
18 Posts |
 Posted - 2004-01-20 : 20:44:24
|
Hi,I am using SQL Server 2000 sp3, running on Windows 2000 sp4.I found another threads talking about memory leaking but I am not sure if they apply to my case. I have a small application with only about 10 concurrent users keep on adding, updating, deleting records from the database frequently (from 9:00AM - 11:30PM).The problem is, the database is hosted in a low-end server with only 512 MB RAM. When I open up the Windows Task Manager, I can see the memory leak slowly. OK...if you think I am writing my application (in VB6) in the wrong way, then I will assume the memory should recover after all users logout the application after 11:30PM every night. Unfortunately, the memory won't recover unless I shutdown SQL Server and restart it.Strange enough, when the memory drops to the point with 10 MB RAM of available physical memory, it stops dropping any further but keeps going up and down within the range of 8 MB to 10 MB. And the users never experience any performance problem.Am I doing something wrong? Or is it normal? And please don't tell me to buy more RAM as it is not possible at this moment.  HIU |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-20 : 21:05:06
|
| You need to look at your VB code carefully. Make sure that you are destroying your objects properly. Close ADO Recordset and Connection objects when finished, then set them to Nothing. Don't rely on the garbage collection to do it, close and destroy them explicitly. |
 |
|
|
hiuhiu
Starting Member
18 Posts |
Posted - 2004-01-20 : 22:05:58
|
Yes, my program always destroy all ADO Recordset and Connection objects. And I did go through my program a few more times after seeing this strange behavior of the server.As I said, even if I did screw up in one place in my program, will the server be able to re-gain the memory after all users logout the system at 11:00PM and login again at 09:00AM in the next morning?  HIU |
|
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2004-01-20 : 23:59:31
|
| If you are talking about sql server taking all the memory then this is by design, it will take as much memory as it can to cache the data.The only way to stop this is to put a cap on how much memory it will use. SQL is designed to be the only application on the machine and hence use as much ram as you give it. My current server takes about 4 hours to use the full 2gb in the system and it just sits there :) |
|
|
|
hiuhiu
Starting Member
18 Posts |
Posted - 2004-01-21 : 00:43:20
|
scullee, that's exactly what I am talking about. It's scary by watching the memory keeps decreasing while I have no other application running on that low-end server. I didn't know about how SQL Server behave like this as the servers that I was working with were always managed by the 'REAL' DBA. But for this small project, I manage the server by myself.I just hope that the available memory won't drop to 0 KB and then the server freezes!  Thanks a lot for all the replies!HIU |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-21 : 12:22:40
|
| hiuiu, SQL Server is decreasing the amount of memory it is using because it no longer needs it and because another process is requesting it. This is by design. SQL Server will "regain" the memory when it needs it, that's if there is any available.Tara |
|
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2004-01-22 : 20:21:30
|
| I run a Development server on my notebook and sometimes when i do big deletes the server takes all the ram and stops me doing anything else. It made a huge difference when i changed the setting to leave at least 200mb free. Its not a production box so performance of sql isnt a big issue. :) |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-01-23 : 10:34:44
|
| SQL server is designed to detect when the system is low on memeory, It will free up memory from its BPool to allow 10 MB left for the system when it does get to this point. |
|
|
|
hiuhiu
Starting Member
18 Posts |
Posted - 2004-01-24 : 23:06:15
|
| tduggan, thanks for the reply! Since I am so new in managing SQL Server, it just looks like a memory leak when I started the SQL Server on that low-end server with 300 MB available and then watching it to drop to 90 MB in 2 days without any regain.I also try that at home with the similar hardware/software configuration. I wrote a small program to keep retrieving data from the database for a few days. And the meomory drops to about 10 MB and then starts to regain a tiny little bit of memory sometimes. Just like stephe40 said, 10 MB left and then regain...but just a tiny little bit of memory!Well...just thought it should release more memory instead of staying in that low level (10 MB).Thanks for all the replies again!HIU |
|
|
|
|
Memory Leaking