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

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!
LVL 4
Harper McDonaldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You may running on a bug here. Please check if you have the latest updates installed. Check this KB.
0
Harper McDonaldAuthor Commented:
Cool thanks - I'll get back with you, usually our DBA's are up to date on patches but I will ask :)
0
DcpKingCommented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Harper McDonaldAuthor Commented:
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
DcpKingCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Harper McDonaldAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Memory I believe is 12 GB
OS memory? And how much is configured for SQL Server max server memory?
0
Harper McDonaldAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Resource Governor, you mean?
I'm asking about the SQL Server memory configuration.
0
Harper McDonaldAuthor Commented:
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
Harper McDonaldAuthor Commented:
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
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Interesting article and that's why I stand for not using virtual machines on heavy OLTP solutions.
0
Harper McDonaldAuthor Commented:
I agree on that Vitor.  ;)
0
Harper McDonaldAuthor Commented:
Per our DBA:


Our SQL Server setup standards on memory: 4 GB or 10% for OS.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Harper McDonaldAuthor Commented:
Yeah I think the same thing.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
Harper McDonaldAuthor Commented:
4 GB or 10%, whichever is higher.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, seems much better :)
0
Harper McDonaldAuthor Commented:
0
microheadCommented:
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
Harper McDonaldAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Harper McDonaldAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.