February 12, 2020 at 6:46 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 13, 2020 at 8:53 am
Replies to this thread please
https://www.sqlservercentral.com/forums/topic/restore-database-on-azure-vm-fails-2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply