Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-08-29 : 07:46:05
|
| Jay writes "I understand that you only answer SQL server questions... I hope MSDE still qualifies...I am having a problem with MSDE 1.0 (Baby SQL 7.0). This seems to happen regardless of SQL 7.0 Service Pack applied. (Service Pack 0 through Service Pack 4). I have not tried MSDE 2000. I have multiple customers running MSDE 1.0, with varying database sizes, from 30 meg in size to 500 meg in size.In each case (especially surprising on the 30 meg DB), it seems that the sqlserver service begins to "absorb" ALL memory available in the machine throughout the day. After about 24-48 hours, brings the machine down to a crawl. SQLServer service pegs CPU at 100%, choking the server. Service still responds, but queries that take a millisecond begin to take 30-60 seconds. Restarting the service/rebooting the server "resets the clock" so to speak... my clients are now just in the habit of just restarting their Windows 2000 servers in the morning.I would call it a memory leak... but I can't find anything on it, or any clues on fixing it. It's weird to see sqlserver.exe with 30 meg database utilizing 700+ meg RAM.Each of my clients has 5 computers or less.The application using the MSDE program is written in Visual Basic 6.0, using MDAC 2.7. No stored procedures, but some pretty complex queries are used repeatedly (like a 7 table join executed every 60 seconds to refresh a screen being monitored throughout the day). My VB program's memory usage remains constant throughout the day, and it only has a SINGLE connection to the SQL server that it utilizes throughout the code. Stopping the VB program on all workstations does not cause MSDE to release any resources.No SQLMail or anything like that. It's pretty cut and dry.I have searched high and low for memory leaks that pertain to SQL Server/MSDE and the way that I am doing things... but since my installation and use is so simple and straight forward, I haven't been able to find anything.Any help or clues on where to go from here is most appreciated.Jay" |
|
|
Doug G
Constraint Violating Yak Guru
331 Posts |
Posted - 2003-08-29 : 15:21:43
|
| I think sql server/msde will try to use all available memory over time, but it relinquishes memory back when other apps need it.With enterprise manager I believe you can edit the memory limits for your MSDE server. There is probably a sp or osql command to do the same memory configuration.======Doug G====== |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-29 : 15:27:30
|
| SQL Server 7.0 had a memory leak in service pack 1 or 2 (can't remember which), but I'm not sure if that is true for MSDE. Just install service pack 4 since this one is known not to have a memory leak.If this is a dedicated database server, then do not change the maximum allowable memory for SQL Server. You only need to change it when you use multiple instances or when other applications are being used on the server. You can back the memory off by 128MB though, so that the OS gets some. It sounds like though that you just need to add more memory. If SQL Server uses all of the memory and slows down at that point, then you should add more. You can check if a bottleneck exists here by running Performance Monitor. You would check for excessive paging in the sqlservr.exe processTara |
|
|
|
|
|
|
|
|
MSDE High Memory Usage