| Author |
 Topic  |
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
 Posted - 10 December 2002 : 18:26:12
|
| The default install for SQL places it memory usage into dynamic mode, as HuwR's said basically that leaves it free to use between 4Mb and roughly all the system has got, never a good thing especially if its actually on the same machine as the IIS Server. imo a well tuned SQL Server should never be left in Dynamic mode, you need to make sure theres headroom left on the server for the OS and anything else that may be running on it. |
Kiwihosting.Net - The Forum Hosting Specialists
|
 |
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 10 December 2002 : 18:35:32
|
| Thank you for all the input, I'll direct the end user to this discussion :) |
Reinsnitz (Mike) |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 10 December 2002 : 19:06:17
|
I have 1.5 gigs of RAM running a P4 2.53 Ghz processor and like I said, I expect SQL Server to use a huge portion of it, but I am losing RAM and not reallocating it back. It will basically drain out completely in a sense within 24 hours! I'm trying to figure out if it is a hardware or software issue right now.
As for setting SQL Server memory, I am re-examining my settings which I have tweaked in the past as described above. I have also tweaked my IIS. One unfortunate thing is my home setup does not really allow me to host the SQL Server separately on its own machine so YES, it is on the same box as my IIS but that has not caused me issues in the past and I've been doing this from home for about 2 years now. Its just recently that things took a turn for the worst. Its manageable but I want to find and resolve the issue.
I manage 3 SQL Servers at work with some pretty meaty applications on top of them. The hardware is quite similar to what I am using at home and I do not experience the problems with the memory. I am totally shutting down my forum on the 12th to do some additional testing so as to eliminate IIS, SQL and any bad coding I may have. I suspect I will still be leaking memory but I need to confirm this. Once I do, I will start looking a little closer at my Install and Hardware setups.
|
 |
Edited by - dayve on 10 December 2002 19:20:51 |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 10 December 2002 : 19:21:51
|
| dayve, forumco.com had the same problem recently, it ended up being a bad hard drive I believe. Are you getting a lot of errors in your logs relating to SQL Server? |
|
|
|
@tomic
Senior Member
USA
1790 Posts |
Posted - 10 December 2002 : 20:50:31
|
I have had this same SQL memory problem. Are you running SQL 7? After reading a lot on the problem I found that it's a known issue with SQL 7. The only way we could resolve the problem was to limit how much memory the server could use.
@tomic |
SportsBettingAcumen.com |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 10 December 2002 : 21:54:03
|
richard: very interesting... I've been suspecting one of my hardrives having issues due to what I thought was unrelated but now I have another lead to look into. I am going to review my logs again a bit closer.
@tomic: SQL 2000 Enterprise |
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 10 December 2002 : 22:16:55
|
| I also had problems with SQL and a dodgy Hard disk, sql would suffer from a bout of dead locking, which after investigation corresponded almost exacly with a series of disk errors reported in the server logs, I shifted all the data and it has been fine since then. |
|
|
|
Carefree
Advanced Member
Philippines
4217 Posts |
Posted - 11 December 2002 : 00:23:21
|
| Another possible culprit could be a for-next loop which never closes - that will gobble memory like no other programming issue. |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 11 December 2002 : 00:27:48
|
| iterating through a recordset with doing an rs.movenext is another good one |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 11 December 2002 : 01:12:22
|
okay, I was playing around and when I used the Service Manager and Stop the services and then restart the services, the available memory jumps way up. Of course it begins to dribble down ever so slowly but this is kind of an indicator that something is wrong with my sql setup.
for example. my available memory was 1 gig but within 1 hour it would crawl down to 875 meg. I stop sql server and it jumped up to 990 meg and then within an hour it crawls down to about 865 meg. kind of weird... |
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 11 December 2002 : 01:18:32
|
This is normal behaviour for SQL, what limits do you have set for SQL is it dynamic, or does it have a set size ?
The SQL server will do this untill it has all the memory it things it needs, if you have set the limt at 512Mb, it will climb steadily until it has claimed 512Mb, if it is dynamic, it will climb each time it requires some more.
It normally takes the SQL server here after startup about 30 mins to reach 1.5Gb of ram, since that is what I have given it. |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 11 December 2002 : 11:17:30
|
*** sees the light ***
I have it set a bit higher than I should, just set it to 512MB now. I should have known this. Thanks for all the help with reviewing my environment. I have to stop assuming Defaults are fine. |
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 11 December 2002 : 13:51:36
|
quote:
Originally posted by dayve
*** sees the light ***
I have it set a bit higher than I should, just set it to 512MB now. I should have known this. Thanks for all the help with reviewing my environment. I have to stop assuming Defaults are fine.
No probs, that what we're here for |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 11 December 2002 : 18:36:44
|
Subject: RE: ISP Use
Date: Tue, 10 Dec 2002 22:14:35 -0800
From: "SBWH.com Support" <support@sbwh.com>
Thanks for you help on this, I will try to post this reply later, since my login is not valid yet. (sbwh)
Sorry for the confusion on this, I was not clear on the problem. We often get messages that a forum no longer works, and when we check, the message is that the database (ms access 2000) may be damaged. (I forget the exact message). The server is often under a short heavy load before this error.
We had an unrelated problem, did some memory dumps, and I got the message below from Microsoft. I mistated that Microsoft said Snitz was trying to access protected or critical memory space.
From MS:
I found atleast 7 threads trying to enter critical section. but, the thing is that it looks like they are entering it and coming out immediatley and I am trying to find of the owing thread of critical section.
All these threads are trying to precess following ASP pages.
(edited to remove unlreated threads)
0:019> !asppages
Thread 19: F:\WWWROOT\WEAPON-SHAPED\FORUM\TOPIC.ASP
Thread 33: F:\WWWROOT\WEAPON-SHAPED\FORUM\TOPIC.ASP
Thread 44: F:\WWWROOT\WEAPON-SHAPED\FORUM\TOPIC.ASP
David
support@sbwh.com
Santa Barbara Web Hosting, Inc.
http://www.sbwh.com
|
Reinsnitz (Mike) |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 11 December 2002 : 19:35:49
|
| sounds like and db driver problem to me, or an overloaded access database, how big is the db, how many concurrent users does he get |
|
|
|
Topic |
|
Topic Locked
