SQL SERVER – Fix Error – Restore Operation Failed for Database Due to Insufficient Memory in the Resource Pool ‘Default’

SQL
6 Comments

SQL SERVER - Fix Error - Restore Operation Failed for Database Due to Insufficient Memory in the Resource Pool 'Default' errorpc Recently I faced a strange error related to SQL Server, during Comprehensive Database Performance Health Check. The error indeed surprised me because as I have been doing performance tuning consulting for over 10 years, this is the first time I walked into this error. Let us see how we fixed the error related to insufficient memory.

While we were fixing SQL Server performance issues with customers, we figured out a very interesting solution to their performance problem. The customer did not have a development environment so we took quickly backup of their database and attempted to restore it on their development box. We were planning to try out our solution on their development system and if it worked on their production system. However, while we were  attempting to restore the database, we found following error.

The database which we were restoring had few In-Memory OLTP tables.

Solarwinds

Msg 41379, Level 16, State 0, Line 0
Restore operation failed for database ‘InMemoryDB’ due to insufficient memory in the resource pool ‘default’. Close other applications to increase the available memory, ensure that both SQL Server memory configuration and resource pool memory limits are correct and try again. See ‘http://go.microsoft.com/fwlink/?LinkID=507574’ for more information.
Msg 3167, Level 16, State 1, Line 2
RESTORE could not start database ‘InMemoryDB’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Fortunately, even though I had never faced this error before I had a solution ready with me.

Solution / Workaround:

What we needed to do was to increase the available memory to the default resource pool in SQL Server. The memory available to default memory pool is usually 25%. We increased that to 90% and our error was disappeared.

ALTER RESOURCE GOVERNOR DISABLE 
GO
ALTER RESOURCE POOL "default" WITH ( MAX_MEMORY_PERCENT = 90 )  
GO 
ALTER RESOURCE GOVERNOR RECONFIGURE   
GO

Have you ever faced such a problem? Do let me know by leaving a comment below.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , , , ,
Previous Post
SQL SERVER – Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication
Next Post
SQL SERVER – Too many dumps generated. CPerIndexMetaQS::ErrorAbort – Index Corruption

Related Posts

6 Comments. Leave new

  • Hi Pinal,

    we are facing the same issue, we have implemented the IN MEMORY DB on our local server with 1 TB HDD & 16 GB RAM with SQL server 2017

    whenever the server got restarted then the DB goes to recovery mode & throws an error “There is insufficient system memory in resource pool ‘default’ to run this query”

    changed the setting to 90% but still facing the issue & unable to recovery the DB.

    DB contains only one table size is only 2.6 GB ,but backup taken its around 6.6 GB.

    Can you please help me on this issue.

    Thanks
    Santosh

    Reply
  • Thanks Carlos.

    Reply
  • Iftari Nuramzan
    May 20, 2019 4:00 am

    Hi,

    i have the same issue which is :

    Msg 701, Level 17, State 13, Procedure g39_ck, Line 1
    There is insufficient system memory in resource pool ‘internal’ to run this query.

    And tried execute the script above :

    ALTER RESOURCE GOVERNOR DISABLE
    GO
    ALTER RESOURCE POOL “default” WITH ( MAX_MEMORY_PERCENT = 90 )
    GO
    ALTER RESOURCE GOVERNOR RECONFIGURE
    GO

    but there is an error like this :
    ‘ALTER RESOURCE GOVERNOR RECONFIGURE’ failed. The resource governor is not available in this edition of SQL Server. You can manipulate resource governor metadata but you will not be able to apply resource governor configuration. Only Enterprise edition of SQL Server supports resource governor.

    Is there other script that support my issue in SQL Server 2008 R2?

    Thanks

    Reply
  • This doesnt work for me though. Still get the same error

    Reply

Leave a Reply

Menu