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
247 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 46

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 46

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 46

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 46

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 46

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 46

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 46

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 46

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
Know what services you can and cannot, should and should not combine on your server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

21 Experts available now in Live!

Get 1:1 Help Now