July 20, 2004 at 6:33 am
We are trying to solve memory problems on a SQL2K server running Enterpise Ed. on a cluster. About once every 2 weeks we get the msg:
Could not perform the requested operation because the minimum query memory is not available. Decrease the configured value for the 'min memory per query' server configuration option.
and we have to stop and restart the SQLserver service.
While running all sorts of DBCC commands I noticed that DBCC CACHESTATS only returns zeros. Why? Since yesterday I am running DBCC MEMORYSTATUS every 2 hours and it looks like the value for Query Memory Objects: Available (Buffers) is only decreasing and never increasing. The last time we got the error, this value was 0.
Are there know issues about memory leaks in SQL2K? Or can anybody point me to a link about how SQLserver go about allocating and freeing query memory\buffers?
Also, what does DBCC memorymap do?
(I know I am using undocumented commands here, but surely the info it returns means something!)
July 21, 2004 at 12:58 pm
This seems is a memory leak problem. You need check the connection is closed properly. Check cursors are closed properly. Apply latest jdbc SP (if app use one), there is non memory leak on the earlier jdbc driver. Do you notice any large query running for several hours ?
July 21, 2004 at 1:13 pm
Maybe the Management / Current Activity / Process Info / Memory Usage column would show you old connections that are using up query memery?
July 21, 2004 at 1:29 pm
How much memory do you have in the server and configued to SQL Server? Do you run it in active/active or active/passive? Do you use Full Text search service? What is SQL Server service pack level? We need more information about your system environment and how exactly the issue occurs.
July 22, 2004 at 8:58 am
2GB Memory, Compaq ProLiant DL580 G2, 2X1396 mHZ
Mem dynamicly configured with MAX 1946
active/active cluster
Full Text search installed and started, but not used.
Version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
The problem has occured about 4 times during the past 6 –7 weeks. (The server has been running since Dec last year). We get no warning, the service desk just let us know that users cannot work anymore because of the memory a problem and we see the following in the errorlog:
2004-07-14 11:48:09.72 spid60 Error: 8651, Severity: 17, State: 1
2004-07-14 11:48:09.72 spid60 Could not perform the requested operation because the minimum query memory is not available. Decrease the configured value for the 'min memory per query' server configuration option..
If we then try running selects on ie. Ysprocesses, we get the same msg and sometimes also the results after that.
The min query mem is configured for 724. We decreased this the 2nd time the error occured.
I am still running dbcc memorystatus every 2 hours and the Memory Objects: Available (Buffers) is still only decreasing. (Since this is not really documented, I don’t know how much this means, but I thought it might be a good idea keeping an eye on this to see if the problem again occurs when this gets very low).
I also just checked the current processes and apart from the system processes, are all logins from today which means that there are no long-running processes which keeps buffers allocated.
Please let me know if you need more info.
July 22, 2004 at 9:15 am
You were probably capable of finding this link yourself but perhaps there are some ideas to be had here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;321363&Product=sql2k
There seem to be a couple of kb and books online topics specifically for Enterprise Edition and clustered memory configurations.
July 23, 2004 at 11:52 am
Can you decrase the "min memory per query" even further to 512KB to see whether same issues occur less frequent than before?
When same problem happens again, Try to removes all cached queries from the procedure cache by "DBCC FREEPROCCACHE" without recycle SQL Server to see if you can run any queries.
Did both SQL Server instances (active/active) run in same node when you experienced the problem? Are any other applications competing memory resources in same machine? Is "min server memory" be set to default 0 on both instances?
You may also start to trace which queries runs prior such error messages appear.
July 23, 2004 at 12:01 pm
The max. memory may set yo hight, too if you are active/active, you should set that total memory from both instances are less than 1500MB (2048MB - 500MB).
Running a trace is good place to check what's running
Run dbcc memorystatus, what you have ?
760 seems is sp2 or earlier, you should go with SP3. I recall ed that cluster require SP3a.
July 29, 2004 at 7:24 am
What does DBCC MEMORYMAP do? I tried it on my test server twice. The first time it came back right away with the standard DBCC message; the second time I let it run for 2 1/2 minutes before killing it.
Thanks...enjoy the day!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply