Keeping SQL Server up-to-date is something I recommend my customers from time to time. One of the tasks I undertake is to check the current SQL Server version information as soon as I get started to work. Though this recommendation looks trivial at the first look, this is often something people don’t take it seriously. Almost in every environment that I have done this exercise, I see them being behind on the Service pack updates majority of the times. Let us learn in this blog post how to fix the error- Disallowing page allocations for database ‘DB’ due to insufficient memory in the resource pool.
I am currently running the latest version of SQL Server 2016 and I installed the SP1. In the recent past, I have seen the following message on my error logs. I have been ignoring this, but thought to investigate the same. The output is:
Message: Disallowing page allocations for database ‘AdventureWorks2016’ due to insufficient memory in the resource pool ‘default’. See ‘http://go.microsoft.com/fwlink/?LinkId=510837’ for more information.
A sample of how it looks inside SQL Server Error Logs is:
On first look this looked like I had something wrong and I was not aware of what to do. I did exactly what the error message said. I went to MSDN for more information.
As suggested by the documentation, I went about Enabling Resource Governor and the error message disappeared. You can also enable the resource Governor capability using the SSMS UI as shown below.
Do let me know if you error seen this error message on your servers? I am not quite sure why this is happening, but I am glad the solution for this is simple and well documented. Thought to share the same with you as I learnt something new recently.
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
Hello Pinal,
Thank you for consistently putting up fabulous post every day – Really Appreciate it !! When I was playing with In-memory OLTP, there was a message in the error log.
Message
Disallowing page allocations for database ‘imoltp’ due to insufficient memory in the resource pool ‘default’. See ‘http://go.microsoft.com/fwlink/?LinkId=510837’ for more information.
When I checked for solution, ask was to enable Resource Governor. I just enabled RG without changing any threshold or memory configuration, issue was fixed.
My understanding is query optimizer prevents a query from using more than 25% of the allocated memory but when RG is enabled, that query is mapped to default resource pool and default configuration for this pool is as below:
CREATE RESOURCE POOL [default] WITH(min_cpu_percent=0,
max_cpu_percent=100,
min_memory_percent=0,
** max_memory_percent=100, **
cap_cpu_percent=100,
AFFINITY SCHEDULER = AUTO,
min_iops_per_volume=0,
max_iops_per_volume=0)
GO
It could be reason why issue disappeared after enabling RG. My understanding might be incorrect and I would love to be corrected and know the exact reason.
Br,
Anil
Good Job Pinal. Hope this error won’t come to me