Unknown resource requirements for restoring a database that contains memory-optimized data
There is no way to determine how much memory would be required for memory-optimized objects contained in a database back file. This would be a basic expectation of any DBA that had to restore a database containing memory-optimized objects.
Consider this scenario:
- a full database backup file that contains both on-disk and memory-optimized data
- 900GB is on-disk data, 100GB is memory-optimized data
- full backup size is large, i.e. 1TB
- you must restore this backup
- on the target server, there is not enough memory to hold all of the memory-optimized data, but you are unaware of that fact
Therefore, you can spend quite a long time restoring the database, as it will create all the files for on-disk data, i.e. mdf/ndf, and then the data/delta files, and only during recovery will it attempt to stream from data/delta files to memory.
When the restore fails due to out-of-memory condition, you will have wasted quite a bit of time.
When you execute RESTORE FILELISTONLY on a database backup, and the backup contains streaming data, there is no way to differentiate the amount of drive space required for a given type of streaming data. That�s because all streaming data has a value of �S� for Type, and it represents FileStream, FileTable, or In-Memory OLTP container.
Rob Nicholson commented
It would be very useful to know how much memory a database requires before kicking off the restore procedure. Allowing administrators how much memory is require would potentially save downtime from avoiding repeated restore attempts
Also on database startup the current messages (SQL 2017) for insufficient memory for database startup are not very helpful. “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”, this is nice but how much memory does it would be nice to know how much it actually needs.