Unknown resource requirements for restoring a database that contains memory-optimized data - by NedOtter

Status : 


Sign in
to vote
ID 2337626 Comments
Status Active Workarounds
Type Suggestion Repros 1
Opened 2/8/2016 11:54:27 AM
Access Restriction Public


Problem 1:
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:

1. a full database backup file that contains both on-disk and memory-optimized data
2. 900GB is on-disk data, 100GB is memory-optimized data
2. full backup size is large, i.e. 1TB
3. you must restore this backup
4. 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.

Problem 2:
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.
Sign in to post a comment.