Cannot start SQL Server 2014 service. Memory Error (resource pool 'internal')

Question

Under Windows 2012 R2 Standard (Fully Updated): When trying to start my SQL Server 2014 Service I get in Application event viewer:

"there is insufficient system memory in resource pool 'internal' to run this query"

Here is the complete ERRORLOG in pastebin.

Significant errors I can see:

2017-07-04 15:32:56.59 spid8s       Failed allocate pages: FAIL_PAGE_ALLOCATION 1
...
2017-07-04 15:32:56.62 spid8s      Error: 701, Severity: 17, State: 123.
2017-07-04 15:32:56.62 spid8s      There is insufficient system memory in resource pool 'internal' to run this query.
2017-07-04 15:32:56.65 spid8s      SQL Server shutdown has been initiated
2017-07-04 15:32:56.68 spid8s      Error: 701, Severity: 17, State: 123.
2017-07-04 15:32:56.68 spid8s      There is insufficient system memory in resource pool 'internal' to run this query.
2017-07-04 15:32:56.77 spid8s      SQL Server shutdown has been initiated

This is a RAM Map screnshot of my server.

This machine is hosted in a VPS, and technical support says the "Driver locked" RAM is assigned on demand, so I can't find why SQL Server throws this memory error. Any ideas??

Answer 1

This machine is hosted in a VPS, and technical support says the "Driver locked" RAM is assigned on demand, so I can't find why SQL Server throws this memory error. Any ideas??

Yes. Your hoster has "borrowed" your VMs memory and given it to another VM. The "Driver Locked" memory is a "Baloon Driver" that both VMWare and Hyper-V use to enable dynamic memory allocation to VMs. That memory appears to belong to the VM, but is actually held by the hypervisor, and may be currently being used by other VMs.

Not all operating systems support actually changing the amount of memory they use without a reboot. In Windows this capability only appeared in Windows 10/Windows Server 2016. So the way hypervisors handle this is they install something inside the VM to allocate memory. Then this memory is actually removed from the VM and is available for other VMs. Typically they use a driver (as opposed to a user process), commonly called a "Baloon Driver".

So the almost 12 GB are "Driver Locked" by the VPS manager is not actually available. You need to ask your hoster to give your VM more memory.

It may be the case that the hypervisor is supposed to respond to memory pressure inside the VM adding memory to the VM and reducing the amount locked away in the Baloon Driver. But if so, this is just not working well enough to enable your SQL Server to start, and they need to bump up the startup memory for your VM a bit.

On the other hand, if you paid for 16GB of ram, you might want to look for a new hoster who doesn't engage in such shenanagans.

Answer 2

This could have happened, if we have set the max server memory below recommended practices, and have also added the SQL Server Service Startup logon Account to "Lock Pages in Memory" option at OS policies. So if the memory is too low and this option is enabled the SQL service will not start as it has to lock the pages in memory and we have set the wrong max server memory. To rectify this error we need to remove the service Log on Account from this OS policy and then start the service. Once the Service has been started, please set the max server memory in SQL properties to correct value. After that, set the policy "Lock pages in Memory" again with SQL Service Logon and restart the SQL service.

Regards, Ibrahim