Failing to restore a database having 'In-memory OLTP' database

  • Article

A few days ago, encountered a weird scenario where a  'In-memory OLTP' database had to be restored from its backup and it was failing to restore - precisely getting stuck in the Recovery state.  

Checked the error logs and found the below log snippets and it was a SQL server 2016 SP1 instance.

 

2018-10-30 08:41:23.010 spidxx Starting up database 'xxxx'.
2018-10-30 08:41:23.170 spidxx [INFO] HkHostDbCtxt::Initialize(): Database ID: [x] 'xxxx'. XTP Engine version is 2.9.
2018-10-30 08:41:23.350 spidxx [INFO] HkHostDbCtxt::Initialize(): Database ID: [x] 'xxxx'. XTP Engine version is 2.9.
2018-10-30 08:41:32.950 spidxx [INFO] Database ID: [x]. Deleting unrecoverable checkpoint table row (id: 2).
2018-10-30 08:41:38.550 spidxx Recovery completed for database xxxx (database ID x) in 16 second(s) (analysis 9578 ms, redo 0 ms, undo 1 ms.) This is an informational message only. No user action is required.
2018-10-30 08:41:38.580 spidxx CHECKDB for database 'xxxx' finished without errors on 2018-10-24 18:04:24.007 (local time). This is an informational message only; no user action is required.
2018-10-30 08:41:38.580 spidxx [WARNING] Database ID: [x]. Container ID: [{xx}]. Container Path: [E:\SQLData\x\]. Failed to allocate free file in container, will try again later. Error: 0x88000028.
2018-10-30 08:41:38.580 spidxx [WARNING] Database ID: [x]. Container ID: [{xx}]. Container Path: [E:\SQLData\x\]. Failed to allocate free file in container, will try again later. Error: 0x88000028.
2018-10-30 08:41:38.580 spidxx [INFO] Database ID: [x]. Failed to open/create file of type '2' in container 'E:\SQLData\x\'. Error code: 0x88000028. Retrying the operation (1).
2018-10-30 08:41:38.580 spidxx Error: 41355, Severity: 21, State: 1.
2018-10-30 08:41:38.580 spidxx An XTP checkpoint operation encountered an error ('XTP internal error 0x88000028') while processing log record ID (335:34992:370) for database 'xxxx'. Checkpoint processing has terminated.
2018-10-30 08:41:38.580 spidxx Database xxxx was shutdown due to error 41354 in routine 'HkHostControllerCtxtImpl::WorkLoop'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.

 

Thus we found that the database attempted to start, followed by the database context initializing. Post which the database failed to allocate free file and thus verified the storage to find if there were any space constraints. Also we find that the XTP Checkpoint operation also encountered an error. Finally we had the the database shutdown.

On research we found that by disabling all checkpoint activity by bypassing the controller, we could rectify the issue and thus we enabled the trace flag 9918 but only after upgrading the SQL server to 2016 SP2.

Thus we have the issue resolved and the restore was successful !!

Hope it helps!! Happy restoring!!