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://blog.sqlauthority.com)
8 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
you need to check max server memory.
it works for me , thanks !
Thanks Carlos.
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
This doesnt work for me though. Still get the same error
Msg 5181, Level 16, State 5, Line 5
Could not restart database “databaselog”. Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
Msg 41316, Level 23, State 3, Line 3678
Restore operation failed for database ‘databaselog’ with internal error code ‘0x88000001’.
I have faced this following error on AG secondary replica and I had to resume in the database in order to begin the sync again, any idea why this happened?
[ERROR] HkHostRedoLogRecords(): Database ID: [6] ‘databasename’. Failed to redo log record at LSN 002EC8EB:000691E5:0001.
Error code: 0x84000004. (sql\ntdbms\hekaton\sqlhost\sqlmin\hkhosttxmgmt.cpp:2103)
Error: 41316, Severity: 23, State: 7.
Restore operation failed for database ‘databasename’ with internal error code ‘0x84000004’.
Always On Availability Groups data movement for database ‘databasename’ has been suspended for the following reason: “system” (Source ID 2; Source string: ‘SUSPEND_FROM_REDO’). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
Parallel redo is shutdown for database ‘databasename’ with worker pool size [16].
Error: 3313, Severity: 21, State: 2.
During redoing of a logged operation in database ‘databasename’ (page (0:0) if any), an error occurred at log record ID (3066091:430565:1). Typically, the specific failure is previously logged as an error in the operating system error log. Restore the database from a full backup, or repair the database.