June 2, 2004 at 2:16 am
Our SQL2K server has been working with no problems for months. In the last 2 - 3 weeks, it has decided to eat up all available memory and not releasing any back to the OS and SQL2K will eventually hangs, the only way to get it going again is stop and restart the server.
SQL2K Spec
W2K with latest SP, SQL2K with SP3.
The server memory is set to dynamically. (Tried manual setting, it was the same)
Server has 4Gb of RAM, 20Gb free disk space, database is currently running at 1Gb in size.
Default min and max memory setting defined.
Only SQL2K server is loaded and being used on this server.
After stopping and restarting the server at 8:30 this morning, I ran dbcc memorystatus and the results are as followed for Dynamic Memory Manager Buffer:
Stolen 2464
OS Reserved 1648
OS Committed 1615
OS In Use 1604
General 1829
QueryPlan 2093
Optimizer 0
Utilities 9
Connection 62
The following was taken at 9:05 am
Stolen 18680
OS Reserved 1672
OS Committed 1639
OS In Use 1624
General 2092
QueryPlan 17587
Optimizer 0
Utilities 9
Connection 126
As you can see, I'm in real trouble, by 4 o'clock this afternoon, the server will stop again and I will need to stop and restart it. Has anyone got any idea how to fix this one? Please help.
TIA
Ken
June 3, 2004 at 3:29 am
SQL server is monster in dealing with Memory .. so as you put more momory .. he use it all ..
and it is recommended to:
1- add more momory to your Server .. and the performance will be much better 2- do not execute any other programs on your SQL Server PC (this will cause problems later with your SQL Server)
the solution to this problem is to edit your SQL server properties and adjust your memory to be FIXED (and give it a nuber that is near to our memory .. like 200 M if your Memory is 256 M)
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
June 3, 2004 at 6:06 am
Thank you for your reply.
The server has currently got 4Gb RAM, I have tried setting the memory to fixed starting with 0 min to 1536 max. Reason for 1536 is because it is running standard edition and I read it somewhere that it can only access up to 2Gb of RAM, plus the fact that the server hangs at 1.7Gb of memory usage (Task Manager - Processes). But that didn't make any difference. The server is a dedicated for SQL used, only apps like virus checker, powerchute and other essential services running on it.
Any other ideas?
TIA
Ken
June 3, 2004 at 6:41 am
First things first, setup performance monitor to alert you to when you are reaching your memory threshold and check if your cache is being flushed too.
Some suggestions:
- have you been opening cursors or recursive cursors and not closing them? remebering of course that your developers may be opening server side cursors and not releasing them. Use "SQLServer:Cache Manager" cursors to check this.
- how many connections are open (keeping in mind that each connection requires up to 24kb)
Good luck, drop us a line when you resolve this (pray it aint hardware related).
Max
Max
June 3, 2004 at 10:14 am
My experience with SQL not relasing memory are
1. not close cursors
2. using jdbc driver
3. using OA_* extended proc.
Take a look any of this happen to you. Not closed cursors is a programming fix. jdbc driver may can relieved by applying the latest patch. Using OA_* extended SP. No solution.
June 3, 2004 at 10:17 am
The other thoughts.
Microsoft specfic say
DO NOT
1. install anti-virus on the SQL server
2. install disk keeper or alike
both are using kernnel space and could interfere with SQL server.
June 4, 2004 at 1:15 am
I looked at the ASP code, to my horror, most connections were not being destroyed and some of them were not closed after the page has been processed. So I spent the last two days checking and changing some 400+ pages, setting used connections and recordsets to nothing after they are no longer needed. Thought I had cracked it, but the server stopped again!!
There is no Disk Keeper or any defrag program installed on the machine, however, Symantec AV is installed, I have stopped it this morning. We'll see if this works. If there are anymore suggestions, please post through.
TIA
Ken
June 4, 2004 at 10:56 am
I had a similar problem as few days ago. Running on SQLServer 2k sp3, 4Gb Ram and 2 processors. System was hanging when SQLServer was using 1.7Gb of memory. I found some 'unable to allocate 64K contiguous memory' errors in the SQL Server logs.
Tracked the problem down to one very large SQL statement with 19 Left Joins in it. I am sorry to say that I had added the last left join and not noticed a problem on my test system. When I looked at the execution plan the last 4 Left Joins were increasing the execution time and memory required exponentially. I split the SQL into two and the problem went away. The problem seems to be a limit on how many left joins there are before SQLServer starts doing Hash Match/Right Outer Joins to link the last few tables together.
Regards
Peter Tillotson
June 4, 2004 at 11:50 am
I would like to see where Microsoft says that SQL Server does not use more than 2GB. Thank you.
June 4, 2004 at 12:23 pm
I think 2GB limitation is on standard edition
June 4, 2004 at 1:05 pm
You might want to consider sp3a. sp3 had a memory leak.
June 6, 2004 at 1:29 am
I just want to say that there is no problem if you put any antivirus program on your server machine. and of course exclude the sncan of mdf, ndf and ldf files .. and the windows pageing file (swap memory file)
Alamir Mohamed
Alamir_mohamed@yahoo.com
June 7, 2004 at 6:37 am
Links where you can find the memory limitation of 2Gb on SQL2K standard edition.
http://www.mssqlcity.com/FAQ/General/sql_server_2000_editions.htm
http://www.2000trainers.com/article.aspx?articleID=44&page=2
http://www.dbforums.com/archive/index.php/t-941740.html (This one was quoted by SQL Server MVP, so it must be true).
The problem is still intermittent, it worked all through the weekend without problems. However, because of the usual Monday workload, I had to stop and restart the thing this morning. I will now look at if there are any dodgy queries that need sorting out.
TIA
Ken
June 7, 2004 at 11:56 am
Do you have any switches in boot.ini file /3 gb or /PAE
June 8, 2004 at 4:16 am
Just the standard line W2K put in during installation.
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect
The thing stopped again, we have narrowed it down to the database rather than the server as first suspected. We have analysed a few large queries that are frequently used and have taken out a few bits and pieces from them, they are now running a lot quicker, we are monitoring the progress.
We are keeping our fingers crossed, will update again.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply