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

You may running on a bug here. Please check if you have the latest updates installed. Check this KB.

Cool thanks - I'll get back with you, usually our DBA's are up to date on patches but I will ask :)

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

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.

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?

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

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.

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.

Memory I believe is 12 GB

OS memory? And how much is configured for SQL Server max server memory?

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]

Resource Governor, you mean?
I'm asking about the SQL Server memory configuration.

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.

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

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

Select allOpen in new window

Interesting article and that's why I stand for not using virtual machines on heavy OLTP solutions.

I agree on that Vitor.  ;)

Per our DBA:

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

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.

Yeah I think the same thing.

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.

4 GB or 10%, whichever is higher.

Ok, seems much better :)

Trying this:   http://support.microsoft.com/kb/2769594

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

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