Sunday, June 5, 2016

Database Restore Error of a DB with In-Memory OLTP objects

Just installed SQL2016 Core edition and tried to restore AdventureWorks2016 Database on the engine.
However got an unusual error I've never seen before:

The full Error text is:
Msg 41379, Level 16, State 0, Line 0
Restore operation failed for database 'AdventureWorks2016CTP3' 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 'AdventureWorks2016CTP3'.
 

Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.


I've followed the link and have used the suggested script. However, recommended 70% was not enough and I had to increase it up to 90%.
I also closed all apps on the server and stopped some services.

Here is the script I've used:
ALTER RESOURCE GOVERNOR DISABLE
GO
ALTER RESOURCE POOL "default" WITH ( MAX_MEMORY_PERCENT = 90 ) 
GO
ALTER RESOURCE GOVERNOR RECONFIGURE  
GO


After that everything went OK. Still only a question about these magical 13 percent:

It looks like SQL Server has one more caveat for DBA's during restore process.


No comments:

Post a Comment