4

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.

rammap

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??

  • 1
    which process is taking up the memory? – Stijn Wynants Jul 4 '17 at 15:01
  • No process is actually taking up de memory. In taskmgr If you sum the memory by system processes it takes about 2GB. As you can see in My RAM Map, almost 12 GB are "Driver Locked" by the VPS manager, but actually "available" – Jack Casas Jul 4 '17 at 15:22
  • 1
    What is the Max Server Memory Allocation value set in your database instance? – Raidenlee Jul 4 '17 at 17:24
  • Process physical memory low 1 plus Page Alloc Potential -640 both of these means memory available for SQL Server process is low and there is no page to allocate for BP so potential is negative. What is max server memory and what is total RAM on system ? It seems like accidentally you have put lower value for max server memory. You must grant SQL Server service account LPIM and see if SQL Server comes online. – Shanky Jul 4 '17 at 17:51
  • If that the case, how can I reconfigure thi max memory setting if I can't event run the service? I tried -F startup parameter with no luck. Also granted LPIM in gpedit.msc for the service account. No luck – Jack Casas Jul 4 '17 at 22:44
2

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.

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.