Solved

SQL Cluster Error - Event ID 701, ’ There is insufficient system memory in resource pool 'internal' to run this query.’

Posted on 2015-02-23
26
232 Views
Last Modified: 2015-09-15
Event ID 701, ’ There is insufficient system memory in resource pool 'internal' to run this query.’

I have a four node SQL cluster - Windows 2012 and the latest version of SQL.  It has been going offline with this error message - A lot of our clusters here are having issues bouncing back and forth as well...Has anyone see issues like this with SQL clusters in Windows 2012?

Thank you!
0
Comment
Question by:Harper McDonald
  • 13
  • 8
  • 2
  • +2
26 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40627922
You may running on a bug here. Please check if you have the latest updates installed. Check this KB.
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40628054
Cool thanks - I'll get back with you, usually our DBA's are up to date on patches but I will ask :)
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40628136
Harper,
This has been seen before, and commented on by Pinal Dave here back in 2007! Actually, the comments are of most interest, as they indicate that MS didn't fix the problem back then.

MS themselves address it for 2008 here, but, basically, just say that you don't have enough memory for the query, so free some up! None too helpful.

One thing you might check out is your actual settings for memory size. Many people  don't set the max memory to what they actually have, but leave it at the default - 2147483647 MB - which is way more than any machine can actually address! Setting it to what you really have (minus some for Windows!) might result in queries not getting allocated so much memory that they cannot run.

In the end, look at the queries that are causing this and see if you can't rewrite them to use less memory. That may be the only way out in the end

hope this helps


Mike Irwin
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40628138
Mike, those articles are very old. Harper tagged this question a SQL 2012 so I posted a link to an hotfix for this version of SQL Server but let's wait for his feedback.
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40628148
The DBA said he of course already has that installed...He also told me that the problems we are having is only SQL clusters ran in ESXi 5.5 - Physical servers are fine...?  Any thoughts on that?
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40628161
Hi Vitor,

You're absolutely right - the articles are quite old. My point in citing them was to show that this isn't necessarily a brand-new problem, but that it's something that MS knows about and obviously doesn't think is a problem that it should be solving. Therefore maybe it isn't actually a problem with the tool, but a problem with the way we're using it. Rather like getting a message back from your screwdriver that the hole in the screw head is too small for the bit on the screwdriver (so use a larger screw or smaller bit!).

The KB you refer to seems to offer hope for users of AlwaysOn, but I don't see that Harper is using that here, or am I mis-reading the KB?

Mike
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40628164
Any thoughts on that?
Yes but I think you wouldn't like to hear it :)
For some reason I don't install a SQL Cluster in virtual machines but I think you won't go back on this decision so how's the memory configuration set for the nodes? OS memory and MSSQL memory.
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40628415
Memory I believe is 12 GB...so should be ok.  I'm calling Microsoft to see what's up, maybe they can shed some light.  I'll hopefully post an answer to this...If they can't fix it I'll call VMware as well.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40628426
Memory I believe is 12 GB
OS memory? And how much is configured for SQL Server max server memory?
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40628431
Not sure on that one - I told the DBA he should use the memory governor that is built into SQL....[I think that's what it's called]
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40628523
Resource Governor, you mean?
I'm asking about the SQL Server memory configuration.
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40628579
Yes that what I meant - Not sure on the SQL server memory configuration.  What do you suggest and I can run it by my DBA.
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40629123
Root cause seems to be from this as far as the cluster(s) going offline *if* they are VM's:

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2039495
0
Promote certifications in your email signature

Has your company recently won an award or achieved a certification? They'll no doubt want to show it off. Email signature images used to promote certifications & awards can instantly establish credibility with a recipient and provide you with numerous benefits.

 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 40629406
You can find out what the memory setting is by having the DBA run this:

exec sp_configure 'show advanced', 1
reconfigure
exec sp_configure 'max server memory'
exec sp_configure 'show advanced', 1
reconfigure

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40630393
Interesting article and that's why I stand for not using virtual machines on heavy OLTP solutions.
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40630424
I agree on that Vitor.  ;)
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40630519
Per our DBA:


Our SQL Server setup standards on memory: 4 GB or 10% for OS.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40630536
I can't really understand that statement.
So, in a machine with 12GB, only 1.2GB it's reserved for OS? If so, it's very few memory left for a 64bit machine.
1
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40630557
Yeah I think the same thing.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 40630772
Well, the way I read his statement is that it is either 4GB of RAM or 10% if the RAM amount makes the 10% greater than 4GB. But if it is not the way I am thinking and it is truly 1.2 GB for the OS, that is a serious problem.
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40630802
4 GB or 10%, whichever is higher.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40630810
Ok, seems much better :)
0
 
LVL 4

Author Comment

by:Harper McDonald
ID: 40649956
0
 
LVL 3

Expert Comment

by:microhead
ID: 40970504
Here's a nice sql memory calculator. Takes server memory and finds the sweet spot for SQL and server memory. This is just an indication but it will help you on you way..
Max-Server-Memory-Calculator_en.xlsx
0
 
LVL 4

Accepted Solution

by:
Harper McDonald earned 0 total points
ID: 40971298
Turns out that McAfee HIPS has a known memory leak with SQL which is being addressed in it's latest release.  We run HIPS on our SQL servers in our DMZ -- don't it it McAfee sucks balls.
0
 
LVL 4

Author Closing Comment

by:Harper McDonald
ID: 40977691
Turns out that McAfee HIPS has a known memory leak with SQL which is being addressed in it's latest release.  We run HIPS on our SQL servers in our DMZ
0

Featured Post

Don't lose your head updating email signatures!

Do your end users still have the wrong email signature? Do email signature updates bore you or fill you with a sense of dread? You can make this a whole lot easier on yourself by trusting an Exclaimer email signature management solution. Over 50 million users do...so should you!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now