Sql-server – Database Stuck ‘In Recovery’ with command XTP_DB_RECOVERY

memory-optimized-tablesrecoverysql server

We recently converted a table to memory optimized data. Our backups have all ballooned (3x300GB files to 3x600GB files for Full, 3x50GB to 3x250GB for Diffs), and startup has been getting progressively slower.

In an attempt to stave off these issues we converted the table in question to SCHEMA_ONLY durability, but now the database will not leave 'In Recovery' status.

The errorlog originally was updating on recovery status every 20 seconds, predicting analysis would take ~9days to complete, after roughly an hour however, the updates ceased.

SP_WHO2 shows only one process accessing the DB in question with the command XTP_DB_RECOVERY, however SELECT * FROM sys.dm_db_xtp_checkpoint_files returns no rows.

Do I have any recourse? Or way to see the estimated time remaining for this XTP_DB_RECOVERY command?

Best Answer

It's in a restoring state so you need to create and log a backup. (see below)

    BACKUP DATABASE [earnings] 
TO DISK = N'c:\sql\earnings.bak' 
    WITH NOFORMAT, NOINIT, NAME = N'earnings-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10
    GO

    BACKUP LOG [earnings] 
TO DISK = N'C:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak' 
    WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10

Once we have the backups, we will restore the backups.

In order to restore the full and log backup we need to use the NORECOVERY option for the full restore. So, if we just restore the full backup as follows:

RESTORE DATABASE [earnings] 
FROM DISK = N'c:\sql\earnings.bak' WITH NORECOVERY, NOUNLOAD, STATS = 10

The database will now be in a restoring state. If we forget to restore additional backups, the database will be stuck in this mode.

object explorer To finalize the restore and access the database we need to restore the log backup as follows:

RESTORE LOG [earnings]
FROM DISK = N'c:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak'
SQL Server database in RESTORING state after doing backup log with NORECOVERY

Another reason your database can be in restoring state is when you backup the tail of the log using the NORECOVERY option as shown below.

    BACKUP DATABASE [earnings] 
TO DISK = N'c:\sql\earnings.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'earnings-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10
    GO

    BACKUP LOG [earnings] 
TO DISK = N'C:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak' 
    WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07', 
SKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 10

This will cause the database to change to a restoring state.

To fix this you can restore the database backups as shown above.

Make a SQL Server database in RESTORING state accessible without restoring backups
If the database is stuck in the restoring state and you don't have additional backups to restore, you can recover the database using the following command:

RESTORE DATABASE [earnings] WITH RECOVERY

Once you issue this command, the database will be useable, but you won't be able to restore any additional backups for this database without starting all over again with the full backup.

Read up on "SQL Server Database Mirroring" also.

Related Question