Restore database on Azure VM fails

  • pooyan_pdm

    Hall of Fame

    Points: 3007

    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

    • This topic was modified 1 week, 3 days ago by  pooyan_pdm.
    • This topic was modified 1 week, 3 days ago by  pooyan_pdm.
    • This topic was modified 1 week, 3 days ago by  pooyan_pdm.

    Pooyan

  • as1981

    SSCrazy

    Points: 2617

    I think this is also posted here https://www.sqlservercentral.com/forums/topic/restore-database-on-azure-vm-fails

  • pooyan_pdm

    Hall of Fame

    Points: 3007

    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

  • anthony.green

    SSC Guru

    Points: 112358

    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.

  • pooyan_pdm

    Hall of Fame

    Points: 3007

    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.

    • This reply was modified 1 week, 2 days ago by  pooyan_pdm.

    Pooyan

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply