February 12, 2020 at 6:48 pm
I'm trying to restore a database containing memory optimized tables to a standard edition of SQL Server 2017 on an Azure virtual machine (win server 2016), also the database files would be located on Azure Premium Storage Account on the same region (once it successfully gets restored) and not on the disks attached to the VM. The database size is around 1TB and the database has a single memory optimized table with the total memory optimized size of around 30GB. The virtual machine I'm trying to restore the db on is a large isolated VM with nothing else running on it. It has 256GB of memory and 8 CPU cores.
On every attempt the restore fails when it gets to around 96% and while trying to read the memory optimized checkpoint files from the backup and construct them on the Azure Storage Account.
RESTORE DATABASE [MYDB] FROM
URL = 'https://backupStorageAccount.blob.core.windows.net/myfolder/backup_1.bak',
URL = 'https://backupStorageAccount.blob.core.windows.net/myfolder/backup_2.bak',
URL = 'https://backupStorageAccount.blob.core.windows.net/myfolder/backup_3.bak' ,
URL = 'https://backupStorageAccount.blob.core.windows.net/myfolder/backup_4.bak'
WITH
MOVE 'datafile' TO 'https://MyPremiumStorageAccount.blob.core.windows.net/mydb/mydb.mdf',
MOVE 'logfile' TO 'https://MyPremiumStorageAccount.blob.core.windows.net/mydb/mydb_log.ldf',
MOVE 'InMemory_Data_XTP_0' TO 'https://MyPremiumStorageAccount.blob.core.windows.net/mydb/xtp/inMemory_data_xtp_0',
MOVE 'InMemory_Data_XTP_1' TO 'https://MyPremiumStorageAccount.blob.core.windows.net/mydb/xtp/inMemory_data_xtp_1',
MOVE 'InMemory_Data_XTP_2' TO 'https://MyPremiumStorageAccount.blob.core.windows.net/mydb/xtp/inMemory_data_xtp_2',
MOVE 'InMemory_Data_XTP_3' TO 'https://MyPremiumStorageAccount.blob.core.windows.net/mydb/xtp/inMemory_data_xtp_3',
MOVE 'InMemory_Data_XTP_4' TO 'https://MyPremiumStorageAccount.blob.core.windows.net/mydb/xtp/inMemory_data_xtp_4',
MOVE 'InMemory_Data_XTP_5' TO 'https://MyPremiumStorageAccount.blob.core.windows.net/mydb/xtp/inMemory_data_xtp_5',
MOVE 'InMemory_Data_XTP_6' TO 'https://MyPremiumStorageAccount.blob.core.windows.net/mydb/xtp/inMemory_data_xtp_6',
RECOVERY, REPLACE;
error message:
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.The operating system returned the error '1450(Insufficient system resources exist to complete the requested service.)' while attempting 'FileHandleCache::OpenFile' on 'https://mypremiumstorageaccount.blob.core.windows.net/mydb/xtp/inMemory_data_xtp_3/$HKv2/{0532D36E-D8E0-418D-9374-48F52D97850E}.hkckp' at 'filecache.cpp'(839).
These are CFP or Checkpoint File Pares that store the memory optimized data on disk, the maximum file size for this table is just around 600MB, I noticed on each attempt the failure happened on random files even on some small delta file with just 8MB in size. It usually successfully restores the CFPs in "inMemory_data_xtp_0", "inMemory_data_xtp_1", "inMemory_data_xtp_2" containers but fails while trying to restore the files in 4th or 5th container.
I didn't find anything on Google related to this, the closest thing was a few old KBs suggesting to tweak some Windows registry keys to change the pooled/un-pooled memory on the windows side, but I'm not sure that's the right way to go as it was for Win 2003 and I also monitored the pooled/un-pooled memory consumption through task manager and it didn't exceed 300MB during the restore.
Any suggestion or guidance is greatly appreciated.
Thanks
Pooyan
February 12, 2020 at 11:18 pm
I think this is also posted here https://www.sqlservercentral.com/forums/topic/restore-database-on-azure-vm-fails
February 12, 2020 at 11:48 pm
yes I posted it also on Azure SQL forums to make sure it's not missed, as the database/VM is hosted on Azure and the root cause could be very well related to the deployment environment or something purely OS related.
Pooyan
February 13, 2020 at 8:52 am
Please don't cross post issues, it fragments the replies and makes it harder to manage.
Out of interest did the backup come from the same instance or was it from somewhere else? If somewhere else what was the edition, Dev/Ent/Std?
Just asking as the memory optimised size for STD is 32GB. Where as it's unlimited for DEV/ENT, so if it's come from a different edition, you need to ensure you've done the math correct on the memory space to restore that into the limits for STD.
February 13, 2020 at 5:39 pm
Yes the source instance is a Standard Edition as well and I initially tried to restore the database on the very same instance as the source but it failed. Also as I mentioned by the point of failure in the Restore process only less than half of the CFPs are actually created in the destination file system (3 out of 7 container) I suspect if that's the cause. I will try to see if I can delete some data from the memory optimized table and then try again but my assumption is when the table size is below the Standard Edition limit of 32GB it should be ok to restore it to another standard instance
Thanks for the suggestion.
Pooyan
March 18, 2020 at 8:18 am
Did you ever figure this out? Just curious because I did not see it, but what are your min/max server settings set to on the instance where the restore is failing?
Joie Andrew
"Since 1982"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply