Can't restore database due to insufficient memory

Question

Hi Expert,
In our environment we have SolarWindsOrion monitoring tool and running on SQL Server 2014 Enterprise Edition. We often encounter a issue that is database went to recovery pending. Because after apply patch windows team perform reboot and SQL Server database went to recovery pending.

I requested them prior reboot shutdown the SolarWinds application Services and then shutdown the database cleanly.

I was able to bring the db online from restore from the last full backup. There is a memory optimized table which is growing large. It requires more memory. Once I had a same issue but I tried to restore the database and it failed due to insufficient memory. One of test server I tried to restore the database and allocated more memory like around 160 GB and it was restored fine.

In our production SQL Server Memory is 126 GB. I requested windows team to increase the memory they don't want to do that. In order to prevent this issue in future what can I do?

I have a question can I remove memory optimized table from that database. So that I don't worry about memory issues. Because memory optimized table size is growing. I observed in the resource monitor during the restoration time memory max out.

Will this cause any issue if I remove memory optimized table from the database? I don't have much knowledge on this. Please help

I have found a resource link below. Will that help in my case? I just wanted to clarify since memory optimized table size is growing day by day. I can bind the memory optimized table. Correct me if I am wrong

https://www.mssqltips.com/sqlservertip/3368/setup-a-memory-quota-for-sql-server-memory-optimized-databases/

Answer 1

I'm guessing that the memory optimized table is being used for something that is rather large. In my opinion, in memory tables should really only be used for smaller data sets, things that are queried frequently where the data is accessed often with a lot of reads and not many writes and needs to be returned very quickly.

I think setting up a quota is a good option. At the same time, I think it really needs to be determined if this should be a memory optimized table or not. If the answer is no, then you'll need a strategy to migrate the data over into a new table that is not memory optimized. It may also require looking at alternatives to solve your problem if you have serious performance issues and that's why you're using a memory optimized table perhaps your problems can be handled with a different schema, caching outside of the database and many other possibilities.

Here's a couple good links:

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimization-advisor?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/determining-if-a-table-or-stored-procedure-should-be-ported-to-in-memory-oltp?view=sql-server-ver15

Answer 2

Thanks for answering my question. Yes, this is memory optimized table and total memory allocated to memory optimized objects size is almost 75 GB but in before it was 53 GB. I had 120 GB max memory on SQL. That time I was easily restore the database now I encounter below the error

Msg 41379, Level 16, State 0, Line 0

Restore operation failed for database 'XYZ' 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 3456, Level 16, State 1, Line 2

Could not redo log record (310751:4331:4), for transaction ID (2:-741982062), on page (1:229325), allocation unit 72057637921488896, database 'XYZ' (database ID 7). Page: LSN = (310735:185062:258), allocation unit = 72057637921488896, type = 1. Log: OpCode = 2, context 2, PrevPageLSN: (310749:76109:7). Restore from a backup of the database, or repair the database.

Location:　　　　 "e:\b\s3\sources\sql\ntdbms\hekaton\engine\core\database.cpp":2663

Expression:　　 db->TransactionMap->ComputeCount() == 0

SPID:　　　　　　　　 51

Process ID:　　 7392

Restore was successful but deferred transactions remain. These transactions cannot be resolved because there are data that is unavailable. Either use RESTORE to make that data available or drop the filegroups if you never need this data again. Dropping the filegroup results in a defunct filegroup.

RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.

First thing I would more memory to restore the database and then setting up a quota.

So if I set up a quota will that help in next time? Let's say some reason if I have to restore the database then will it consume less memory during the restoration.

Thanks again!

Answer 3

Now I'm not sure this will work or not but it's worth a shot.

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

Answer 4

I suggest that you either skip having this table as a memory optimized table in the first place, or come up with some way to purge old data. It seems like you are heading for problems in the future unless you handle this some way. Mem opt tables has to fit in memory, there is no option for the database engine to have parts of them on disk. I have a feeling that whoever created this as a memory opt table didn't understand this technology and the ramifications - and possibly this table wasn't a good candidate in the first place!

Answer 5

FYI, This is solarwinds monitoring tool database. Temporary i can make the file offline to bring the db online.