SQL SERVER – Error – Disallowing page allocations for database ‘DB’ due to insufficient memory in the resource pool

SQL
3 Comments

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:

SQL SERVER - Error - Disallowing page allocations for database 'DB' due to insufficient memory in the resource pool insufficent-memory-01

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.

SQL SERVER - Error - Disallowing page allocations for database 'DB' due to insufficient memory in the resource pool insufficent-memory-02

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)

, , ,
Previous Post
SQL SERVER – Rule “Windows Management Instrumentation (WMI) Service” failed
Next Post
SQL SERVER – Log Shipping Copy Job Failure – The Password for This Account Has Expired

Related Posts

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

    Reply
  • Ashish Gupta
    June 11, 2017 9:01 am

    Good Job Pinal. Hope this error won’t come to me

    Reply

Leave a Reply

Menu