File Repair

Database is Being Recovered, Waiting Until Recovery is Finished

Summary: Sometimes, when trying to open a database showing as "In Recovery" or "Recovering" in SSMS, you may encounter the error message, "Database is being recovered. Waiting until recovery is finished." This blog discusses the reasons that may result in this error and the ways to restore the database manually. It also mentions a SQL repair tool that can help you to quickly restore the database.

Table of Contents
  • When SQL Server Database Goes into Recovery
  • What to Do Next
  • Causes behind the Error and Solutions Thereof
  • Alternative Solution
  • FAQs
  • When SQL Server Database Goes into Recovery

    When SQL Server starts crash recovery, the database undergoes three phases of recovery (Analysis, Redo, and Undo) to get back online. The database shows up in any of these states during the recovery phase: 'Suspect', 'In Recovery', or 'Restoring' in SQL Server Management Studio (SSMS). Once recovery is complete, the database comes back online. But at times, while attempting to use the database, the following message appears:

    Msg 922, Level 14, State 1, Line 1

    Database xxx is being recovered. Waiting until recovery is finished.

    You might encounter this message when restarting the SQL Server, restoring the database from backup, or attaching the database.

    What to Do Next?

    Before troubleshooting the issue, check the SQL error log to find out the estimated time left for the database recovery process to complete as follows:

    Recovery of database ‘Database_Name’ (1) is 0% complete (approximately 95 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

    Recovery of database ‘Database_Name’ (1) is 3% complete (approximately 90 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

    Here, you can see that the database is in Phase 1 (i.e., Analysis state) and the percentage of data before recovery is completed.

    Causes behind the Error and Solutions Thereof

    Following are the most common reasons behind the error and solutions to fix it:

    Cause 1 – Large Size of Log File

    The common reason behind slow database recovery is a large-sized transaction log (.ldf) file that generates too many virtual log files (VLFs).

    Solution - Apply Microsoft Cumulative Updates

    Apply cumulative updates released by Microsoft to fix the slowing down database recovery issue – when there are too many VLFs in a log file in SQL Server 2005, 2008, and 2008 R2. For detailed information, see Microsoft KB2455009.  

    Cause 2 – AUTO_CLOSE Property is Turned On

    When the AUTO_CLOSE property is enabled, a database shuts down after all connections to the database are closed. So, when a user attempts to connect to the database, it goes into the recovery phase. Usually, the recovery will be quick as the database is cleanly shut down. But it can slow down the recovery of a heavily used database, as it increases overhead by repeatedly opening and closing the database.

    Solution – Turn off AUTO_CLOSE Property

    To do so, follow these steps:

    • In SSMS, expand Databases.
    • Right-click on the problematic database and choose Properties.

    database properties

    • Click the Options tab in the Database Properties screen and set the Auto Close value to FALSE.

     auto close turned off

    • Click OK.

    Now check if the database opens without any issue.

    Alternative Solution

    Use a SQL recovery tool, such as Stellar Repair for MS SQL to restore your database, stuck in recovery, to its original state. The software helps repair a database (MDF) file and restores all its data. It provides a standard scan mode to quickly scan and repair the database file, and an advanced scan mode for scanning and fixing a large database.

    free download

    The software also provides a preview of all the recoverable components in the repaired file before saving them. This helps you to verify the accuracy of recovered data.

    Check out this video to understand how the software works:

    FAQs

    Q. When trying to restore a database from the backup, a message appears saying that the database is restoring. However, it’s been days now and the database seems to be stuck in the restoring state. How can I make the database come out of the restoring state?

    A. Read the blog SQL Database Stuck in Restoring State to find solutions to resolve the problem.

    Q. Is there a way to find out when the SQL database recovery operation will complete?

    A. You can execute this query to find the most recent log entry for the database that is in recovery mode:

    DECLARE @ Database_Name VARCHAR(64) = 'Add your db name here'

    DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

    INSERT INTO @ErrorLog

    EXEC master..sp_readerrorlog 0, 1, 'Database in recovery', @Database_Name

    INSERT INTO @ErrorLog

    EXEC master..sp_readerrorlog 0, 1, 'Recovery completed', @Database_Name

    SELECT TOP 1

        @DBName AS [Database_Name]

       ,[LogDate]

       ,CASE

          WHEN SUBSTRING([TEXT],10,1) = 'c'

          THEN '100%'

          ELSE SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4)

          END AS PercentComplete

       ,CASE

          WHEN SUBSTRING([TEXT],10,1) = 'c'

          THEN 0

          ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0

          END AS MinutesRemaining

       ,CASE

          WHEN SUBSTRING([TEXT],10,1) = 'c'

          THEN 0

          ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0

          END AS HoursRemaining

    [TEXT])

    FROM @ErrorLog ORDER BY CAST([LogDate] as datetime) DESC, [MinutesRemaining]

     

    progress
    78% of people found this article helpful