Fix Database is Being Recovered Waiting Until Recovery Finished

We all hope that when we’re injured, either physically or mentally, that the journey to recovery is swift and thorough. But what happens when it doesn’t seem that way? When being recovered seems endless, and the wait to be finished altogether seems even longer? We’ve all been there, trying to remain positive throughout the process, pushing onward no matter what. This blog post will explore the question of whether or not being recovered is truly a waiting game. We’ll discuss the challenges of being in recovery and the potential pitfalls of waiting for the process to be finished. We’ll look at how to handle the frustrations and anxieties that come with the waiting period and explore what it takes to remain resilient throughout the entire journey. Finally, we’ll identify strategies for managing these challenges and channeling them into healthy coping mechanisms. This post is for anyone who has ever been frustrated with the long road to recovery and is looking for guidance and advice

Databases: Database ‘MyDb’ is being recovered. Waiting until recovery is finished

Cause 2 – AUTO_CLOSE Property is Turned On

A database shuts down when the AUTO_CLOSE property is set to true and all connections to the database are closed. As a result, the database enters the recovery phase when a user tries to connect to it. As long as the database is properly shut down, the recovery will typically be swift. However, because it increases overhead by repeatedly opening and closing the database, it can delay the recovery of a heavily used database.

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.
  • Now check if the database opens without any issue.

    1 Answer 1
    Sorted by:

    Crash recovery is running on MyDb. Possible causes:

  • The SQL Server service was restarted manually, due to a crash or a server stop/start.
  • A severe error in the database caused the database to be shut down and recovered.
  • Recovery was initiated deliberately by someone executing a RESTORE WITH RECOVERY.
  • The Auto-Close option is set, causing the database to shut down when the last user exits and run recovery to restart when the next connects.
  • If recovery is taking a long time to complete and there doesnt appear to be any activity you may need to restore from backups. If youre feeling brave you should start reading everything you can find about repairing/recovering suspect databases.

    Better answers would result from knowing more about the circumstances that led to the database being in this state. g. what steps you took, what errors are recorded in the SQL error log, etc.

    Thanks for contributing an answer to Database Administrators Stack Exchange!

  • Please be sure to answer the question. Provide details and share your research!
  • Asking for help, clarification, or responding to other answers.
  • Making statements based on opinion; back them up with references or personal experience.
  • To learn more, see our tips on writing great answers. Draft saved Draft discarded.

    What to Do Next?

    Check the SQL error log to determine how much longer the database recovery process is expected to take before starting troubleshooting:

    With 95 seconds left, recovery of database “Database_Name” (1) is only 0% complete. Phase 1 of 3. This is an informational message only. No user action is required. Recovery of database “Database_Name” (1) is currently 3 percent complete (about 90 seconds are left). Phase 1 of 3. This is an informational message only. No user action is required.

    You can see that the database is in Phase 1 (i) in this picture. e. , Analysis state) and the proportion of data still needing to be recovered

    FAQ

    How can I check SQL Server database recovery status?

    dm_exec_requests and sys. An understanding of the various recovery states of the database startup is provided by dm_tran_database_transactions. The stored procedure “sp_readerrorlog” can also be used to obtain information about the status of database recovery.

    Why is SQL database in recovery mode?

    Several factors, including but not limited to an uncommitted transaction during a server crash, shutting down an unclean system, or insufficient space problems, can cause the majority of SQL server databases to become stuck in recovery mode. To access data files, the database must go through a thorough recovery procedure.

    How do I find the SQL Server error log?

    View the logs
    1. In SQL Server Management Studio, select Object Explorer. …
    2. Connect to a SQL Server instance in Object Explorer, and then expand that instance.
    3. If you have the necessary access, locate and expand the Management section.
    4. Select View from the context menu of your right-clicked mouse and then select SQL Server Log.

    Leave a Comment