-645.jpg&url=https://www.stellarinfo.com/article/fix-sql-database-being-recovered.php)
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.
- Click the Options tab in the Database Properties screen and set the Auto Close value to FALSE.
- 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.
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:
Read the blog SQL Database Stuck in Restoring State to find solutions to resolve the problem.
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] |
9 min read
-1112.jpg)
