March 21, 2003 at 2:53 pm
As one of those minor ironies in life, we added a GB of memory to one of our servers, and since then -- what else? -- we've been experiencing memory isses.
About twice a week, the server starts blowing chunks and all database activity grinds to a halt until I reboot it.
The server's Event Viewer shows many iterations of the following:
quote:
Error: 17803, Severity: 20, State: 17Insufficient memory available.
Meanwhile, the SQL Server Error Log throws out messages very similar to the following:
quote:
2003-03-21 15:12:46.77 spid1 LazyWriter: warning, no free buffers found.2003-03-21 15:12:46.80 spid1 Buffer Distribution: Stolen=147768 Free=0 Procedures=157
Inram=0 Dirty=48022 Kept=0
I/O=0, Latched=12757, Other=0
2003-03-21 15:12:46.82 spid1 Buffer Counts: Commited=208704 Target=208704 Hashed=60779
InternalReservation=306 ExternalReservation=0 Min Free=512
2003-03-21 15:12:46.82 spid1 Procedure Cache: TotalProcs=12 TotalPages=157 InUsePages=157
2003-03-21 15:12:46.82 spid1 Dynamic Memory Manager: Stolen=147925 OS Reserved=3768
OS Committed=639
OS In Use=3657
Query Plan=118945 Optimizer=572
General=25747
Utilities=22 Connection=6296
2003-03-21 15:12:46.82 spid1 Global Memory Objects: Resource=21329 Locks=141
SQLCache=115 Replication=2
LockBytes=2 ServerGlobal=43
Xact=3107
2003-03-21 15:12:46.82 spid1 Query Memory Manager: Grants=0 Waiting=0 Maximum=30690 Available=30690
...and it keeps reiterating similar messages until I euthanize the server.
I've searched Knowledge Base and found a couple of similarly-sounding issues, but nothing that matches exactly. I ruled one of the discussions out because it claimed you'd need an abnormally high value for Optimizer memory, and I'm not getting that. I also ruled out the ones that claimed the issue was fixed by Service Pack 3, because I already have that installed.
I have 3 GB of RAM available on this server, and I have SQL Server configured to dynamically allocate. (I've already tried doling it a fixed amount of memory, that didn't help.) So far, unless anyone comes up with an alternative idea, I figure there must either be a memory leak in SQL Server 2000 SP3 (i.e., a bug), or else a hardware problem. The systems guys, by the way, claim they're not seeing anything to indicate hardware issues.
At present, I'm stumped. Any insights out there? I'd love to hear about them.
Edited by - Lee Dise on 03/21/2003 3:21:58 PM
March 21, 2003 at 4:45 pm
don't really have any ideas Lee, but a couple things I wonder about.
Was the server running fine before the memory add? If so, can you remove it for a week?
Is the /3GB enabled? AWE enabled in SQL?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 22, 2003 at 2:15 am
Same as Steve, no specific ideas, but my guess would probably be faulty memory. I'm guessing you now have three 1 GB RAMs in the machine. Have you tried running with just one of the old ones plus the new one, and if so, does the machine still behave in this way?
How much memory does SQL aquire with your dynamic configuration?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 23, 2003 at 7:28 pm
Lee,
what kind of server are you using? We have Dell and we needed more memory back in October. One of the servers had 2 GB and tried to apply one more. It did not work. Not because SQL, but because of the server configuration. This particular server had to receive two cards to keep the server configuration as it should be. We did inserted two one GB cards and everything working pefectly. AWE is the way to go if you have Windows 2000 server. Could this be the solution? I don't know I am just metinoning what we experienced when we did it.
Lucky me, instaead of one GB I got 2 GB!!
March 24, 2003 at 1:47 am
Using AWE on a server with 3 GB memory is not possible, it won't do anything. Of course, I may be wrong in understanding the following as in the server having 3 GB RAM:
quote:
I have 3 GB of RAM available on this server
Lee, do you mean the server has 3 GB from the start, or does it have 3 GB available that you are trying to get SQL Server to use? In that case, AWE could be what you are looking for.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 28, 2003 at 2:52 pm
Sorry I haven't been back in a while. To those who were generous with their time and responded, many thanks!
quote:
Steve Jones: "Was the server running fine before the memory add? If so, can you remove it for a week?"
Answer: Yes it was, but no I can't -- a "turf war" issue. I'm on the software side of our little house. We have to let the system admins, on the hardware side (a.k.a. "the Dark Side" 🙂 ) worry about those things. Right now, they're promising me a new server, but I don't have a time table to hold them to.
quote:
"Is the /3GB enabled? AWE enabled in SQL?"
Neither is enabled.
quote:
chrhedga: "...my guess would probably be faulty memory."
I agree that this seems likely.
quote:
cafemar1: "what kind of server are you using?"
A Compaq Proliant, about three years old. Four 549-MHZ processors. 3 GB RAM, 11 GB swap space.
I may take your advice early next week and try the "shock and AWE" thing. (Sorry, long week...)
Thanks again for your suggestions!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply