9

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?

Improve this question
6
  • What's the exact version number you're on? In SSMS, look at the name of the server, and there will be a set of numbers next to it, or SELECT @@VERSION. Commented Oct 15, 2019 at 18:20
  • 13.0.5492.2, Just updated to CU10 Commented Oct 15, 2019 at 18:30
  • 2
    I would worry that it's related to KB 4520109, which just got added to CU10. I know the symptoms don't sound the same, but the fact that it's related to in-memory restores makes me think there might be a related bug that you're hitting. I'd open a support case with Microsoft. Commented Oct 15, 2019 at 18:40
  • 1
    I appreciate your response. Previously we were able to get up and running by enabling trace flag 9918 (per this article). Ultimately the restore ended up completing after 2 hours of silence from the error log. Commented Oct 15, 2019 at 19:17
  • Try running the query in this db<>fiddle and see if that gives you any information back. Commented Oct 15, 2019 at 21:58

1 Answer 1

Reset to default
-2

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.

Improve this answer
1
  • 1
    If you read carefuly, the error the OP encounters has nothing to do with a RESTORE operation. Commented Dec 10, 2021 at 15:36

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.