April 22, 2004 at 9:39 am
We have several applications connecting to a SQL Server database. Occassionally, they will return errors similar to this, which was pulled from SQL's error log shown at the end of this post. After waiting a few minutes, they will be able to process again. I'm trying to gather information regarding what database activities were going on at the time. The server not only hosts SQL Server but hosts IIS witch Crystal Enterprise installed (blame the sales guys for this!). The reports also would return the error messages.
I did two things: 1) reduced the min query memory from the default 1024 to 512 and 2) change the logging mode from full to simple (this is fine for this customer). Before doing this, certain troubleshooting queries I use would return the error message, but after changed these settings, the queries worked. This should not be necessary. I need to determine the cause and am new to monioring. What should I monitor or trace? I suspect that some new .NET applications running at the same time as some other constantly running programs maybe need tuning. Any help in guiding me would be appreciated. Thanks.
Server facts: Page file is on D drive which has tons of free space. RAM is about 1GB; pagefile size is twice the RAM. C drive had 500MB free space at the time (it has more now).
2004-04-22 09:20:00 - ! [298] SQLServer Error: 701, There is insufficient system memory to run this query. [SQLSTATE 42000]
2004-04-22 09:20:00 - ! [000] Unable to retrieve steps for job Replication agents checkup
2004-04-22 09:20:00 - ! [298] SQLServer Error: 701, There is insufficient system memory to run this query. [SQLSTATE 42000] (ConnExecuteCachableOp)
2004-04-22 09:20:13 - ! [298] SQLServer Error: 8651, 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. [SQLSTATE 42000]
2004-04-22 09:20:33 - ! [298] SQLServer Error: 8651, 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. [SQLSTATE 42000]
smv929
April 22, 2004 at 10:24 am
Do you have a hard cap on the SQL memory usage? It may be trying to grab extra memory and can't due to the other applications that are running on the machine.
April 23, 2004 at 7:07 am
No, I don't have a hard cap on the SQL memory usage. The default setting is set (so that it dynamically determines/grabs what it needs). It's really a default configuration other than the changes I have made mentioned above in response to this situation. What are steps to troubleshoot this. I am going to capture a performance trace and see what could be taking a long to see if any queries are poorly written, called a lot, or maybe require new indexes. Any other suggestions would be helpful because when it happens, every application returns this error. So they basically get shut down.
Thanks
smv929
April 23, 2004 at 7:11 am
I would start out with a hard cap on SQL, this will prevent it and other applications fighting for memory space, give the OS and other apps at least 500Mb if you can, really 1Gb would be better.
April 23, 2004 at 7:17 am
If you have another box to run the trace from, I would do it on a separate box, unless your server isn't loaded.
Also, trace files can get big quick.
April 23, 2004 at 7:26 am
apply latest sql server service packs and try.
April 28, 2004 at 3:02 pm
I will try the hard cap and post my results. Thanks!
smv929
June 2, 2004 at 7:27 pm
Let me know how you get on as I have a very similar issue with a client.
September 11, 2007 at 11:55 pm
'could not perform the requested operation because the minimum query memory is not available',Microsoft has fixed this issue in SP4.But i am getting this error even after applying SP4.Does any body has idea?
-Sangeeta Kallur J
November 1, 2007 at 8:39 am
This is a useful thread in this forum if you have not found it yet size]http://www.sqlservercentral.com/Forums/Topic227262-24-1.aspx
January 30, 2008 at 9:32 am
Did you double check your link?
It does not seem to be related to the problem ...
MTN
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply