Tracking down the cause of Error 701 and "There is insufficient system memory to run this query" messages

Question

Tracking down the cause of Error 701 and "There is insufficient system memory to run this query" messages

DakotaPaul

SSC Journeyman

Points: 83
More actions
August 30, 2010 at 1:50 pm

#144795

We keep getting Error 701 and "There is insufficient system memory to run this query" messages on our SQL 2005 box and it's causing portions of our financial system to fail or work incorrectly. The financial system is provided by a third party vendor who updates the software with fixes once per week.
Unfortunately, when there's a problem, their first reaction is to blame us, so as the DBA here I need to be sure I've covered all my bases and have our SQL box set up correctly before I suggest that it's their software that's the problem. (Not wanting to point fingers, but we have had many, many problems with the software since implementation began. They converted their software from HP/UX to Linux and SQL Server, and seem to be learning the latter as they go. It is not uncommon for them to break the software during one of the weekly updates to "fix" something else.) You should also know I'm still a bit of a newbie DBA so I apologize in advance if I'm providing incomplete or confusing information.
Our setup is a cluster (I didn't set it up and am not very familiar with clusters), with one active server and one passive server. They are all on SQL Server 2005 version 9.00.4035.00, SP3, Standard Edition.
The boxes are running Windows Server 2003 Enterprise Edition version 5.2 R2 (Build 3790.srv03_sp2_gdr.100216-1301 : Service Pack 2) and each have 8GB of physical memory.
We've been running this software for a couple years now, but first saw the 701 and insufficient memory errors a couple months ago. At first they were rare, and we weren't on the latest SQL Server service pack, so after reading this article we updated to the latest service pack.
Eventually the errors occurred more frequently, and I thought maybe SQL Server couldn't access more than 4GB of RAM on the machine because it's 32-bit (newbie, remember?). I found this article and we configured the box and SQL Server to use AWE: the "Use AWE to allocate memory" box is checked, the minimum server memory is set to 1024, the maximum server memory is set to 6144, and I have confirmed in the SQL Server log that it's using AWE.
When the insufficient memory errors occur, we restart the SQL Server service so the financial system runs properly again. We restarted it Thursday the 26th and just got the error again today. Here is a pastebin of the SQL Server log. I don't see any errors or anomalies in there before the "Failed allocate pages: FAIL_PAGE_ALLOCATION 1" error, with the 701's and insufficient memory errors following.
Do we just need to install more RAM, or is it possible that their software is not releasing all the database resources it should be? Or something else? I want to be able to go to my boss and say, "We need to get more RAM/upgrade to Enterprise/etc." or go to the vendor and say, "Our setup is correct. You need to check X."
Thanks for your help, all.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

If the box is dedicated for SQL Server then grant "Lock Pages in Memory" privilege to the SQL Server engine service account.

Thank You,

Best Regards,

SQLBuddy

DakotaPaul SSC Journeyman Points: 83 More actions · Answer 2

Thanks, SQLBuddy. We saw that in this article and already did that when we initially set up AWE.

Leo.Miller SSChampion Points: 13051 More actions · Answer 3

sqlbuddy123 (8/30/2010)
If the box is dedicated for SQL Server then grant "Lock Pages in Memory" privilege to the SQL Server engine service account.

Lock Pages in Memory is a 64 bit option only.

Have a look at the -g startup option in this article.

http://msdn.microsoft.com/en-us/library/ms190737.aspx

This is a little used otion, but I've had this issue before and the solution was to increase this vause to 512.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

DakotaPaul SSC Journeyman Points: 83 More actions · Answer 4

Where are you seeing that, Leo? This article says:

On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.

That doesn't sound to me like it's only for 64-bit systems.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Lock Pages in Memory is a 64 bit option only.

That's not true. It can be used on 32 bit systems too.

Thank You,

Best Regards,

SQLBuddy