Have you ever encountered a situation where your SQL Server database goes into the “InRecovery” state after a restart or restore? If so, you’re not alone. This is a common issue that many SQL Server administrators face. In this blog post, we will discuss the details of the recovery process and the actions you can take to resolve it.

When SQL Server restarts, all databases undergo a recovery process to bring them back online in a consistent state. This process consists of three sub-phases: Discovery, Roll forward, and Rollback.

The Discovery phase involves SQL Server going through the transaction log file (LDF) and building in-memory structures to determine the amount of work required in the next two phases.

The Roll forward phase deals with transactions that were committed but not yet written to the data file (MDF) via a checkpoint. These transactions need to be applied to bring the database up to date.

The Rollback phase handles any uncommitted transactions that need to be undone to ensure the database is in a consistent state.

There are several scenarios where you might encounter the “InRecovery” state:

• Restart of SQL Server
• Database offline and online
• Restore of a database from backup

So, what should you do when you see a database in the “InRecovery” state? The first thing to check is the SQL Server error log. Look for the message “Starting up database ‘DatabaseName'”. This indicates that the recovery process has started.

As the recovery process progresses, you will see messages indicating the completion percentage of each phase. For example:

Recovery of database 'DatabaseName' is 3% complete (approximately 36 seconds remain). Phase 1 of 3.
Recovery of database 'DatabaseName' is 27% complete (approximately 20 seconds remain). Phase 2 of 3.
Recovery of database 'DatabaseName' is 95% complete (approximately 1 second remains). Phase 3 of 3.

Once the recovery process is complete, you will see a message indicating the number of transactions rolled forward and rolled back, as well as the total time taken for recovery.

There are several possible causes for a slow recovery process:

• Huge size of the transaction log file
• SQL Server restarted during a long-running transaction
• Huge number of Virtual Log Files (VLFs)
• A bug in SQL Server (check the Microsoft Knowledge Base for known issues)

If you are experiencing slow recovery performance, you can try applying the relevant fixes provided by Microsoft for your version of SQL Server.

Understanding the SQL Server recovery process is essential for database administrators. By monitoring the recovery progress and addressing any performance issues, you can ensure that your databases are brought back online quickly and efficiently.

We hope this blog post has provided you with valuable insights into the SQL Server recovery process. If you have any questions or need further assistance, please feel free to reach out to us.