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

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.

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 – Insufficient Memory:

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. Follow me on YouTube.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)