March 4, 2005 at 12:04 pm
In the past we had a few queries that failed regularly with an Insuficient Memory error. We traced this down to a problem that occurs in queries with large numbers of left joins. The solution was to use the option (force order) on these querries.
Now we are starting to run into the problem more often (every minute of two) and it is happening on querries that are not that complex. Microsoft, has a couple of articles on this, but has anybody else run into this?
March 4, 2005 at 4:23 pm
What are your server configuration parameters, can you describe more about the machine & sql server installation you are working with. We had this happen, and it was due to the fact that the mem-to-leave space is too small by default. On installation, the mem-to-leave space is 256 MB. Mem-to-leave is the place where out of process programs will run on your sql server. You can specify the size of mem-to-leave, with the -g startup parameter. SQL Server Books online has more information about this. You need to experiment with the exact setting suitable for your system; you could probably go to 384 or 512 with no issues.
March 4, 2005 at 5:17 pm
Thanks for the info
I will try the -g. I think this make sense based on BOL since one of the areas failing is SQLMail which needs to load a .dll. I also noticed that this severs SQL Server is configured to use all of the pysical memory. That does not seem right, since there are other processes that have to run on the server. I guess more is not always better. I reduced this to use about 80%. We will try rebooting with the new parameters this evening and cross our fingers that everything seems better Monday. I am really more of a programmer than a DBA, but when problems come up I still have to deal with them.
March 15, 2005 at 1:05 pm
Did that work ?
Thanks in advance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply