This is one of the common issues that you would see as a DBA in your environment :

Whenever Restart of SQL Server happens, it tries to perform the SQL Server Instance Recovery  and during this process all databases tried to come online (Basically trying to Recover themselves) and they stuck in “In Recovery” mode.

This is the stage where the database has to come back online in a consistent state and it has to go under recovery phases as explained below:

PHASES OF SQL SERVER DATABASE RECOVERY PROCESS

There are four sub-phases with-in the process. Discovery, Analysis, Roll forward and Rollback.

1.     Analysis –  This is the phase where SQL Server will check the LOG File(LDF file) and build the in-memory structures to know how much work is needed in the next two phases. So basically it will find the best LSN starting from which rolling forward can be done during redo phase.

2.       Redo – is the phase during which the changes caused by active transactions (at the time of crash) are hardened onto Data files. In other words; during the shutdown of the database, there might be some transactions which are committed but not written to the data file / disk. (MDF / NDF file via checkpoint)

3.       Undo – is the phase where in, rolling back of the active transactions happens. Which means if there were any uncommitted transactions during the recovery of the database; they have to be Rolled Back in order to bring the database to a consistent state

When does the Database can go into “In Recovery” state?

  • When SQL Server starts up after a crash or shutdown where a database was not shut down gracefully.
  • After a cluster failover.
  • After a database mirroring failover.
  • After an availability group failover.
  • When a database state is changed to ONLINE and crash recovery needs to be run.
  • When restoring a database from backups.
  • When bringing a log shipping secondary database online (this is restoring from backups).

What to do ?

First of all check the SQL Server ERRORLOG .In Errorlog, we should see the very first message in the database (is the name of the database):

Starting up database ‘YourDatabaseName’.

This means the files are opened and recovery is started. After sometime, you should see phase 1.

Recovery of database ‘YourDatabaseName’ (91) 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 ‘YourDatabaseName’ (91) is 3% complete (approximately 90 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

Once the phase 1 is complete, SQL Server will go for Phase 2 and 3 as shown below:

Recovery of database ‘YourDatabaseName’ (91) is 5% complete (approximately 85 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database ‘YourDatabaseName’ (91) is 0% complete (approximately 152 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database ‘YourDatabaseName’ (91) is 41% complete (approximately 40 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database ‘YourDatabaseName’ (91) is 55% complete (approximately 12 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database ‘YourDatabaseName’ (91) is 95% complete (approximately 1 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.

So once the Recovery is completed, you should be able to see messages as follows in the ErrorLog file:

4567 transactions rolled forward in database ‘YourDatabaseName’ (91). This is an informational message only. No user action is required.

2 transactions rolled back in database ‘YourDatabaseName’ (91). This is an informational message only. No user action is required.

Recovery is writing a checkpoint in database ‘YourDatabaseName’ (91). This is an informational message only. No user action is required.

Recovery completed for database YourDatabaseName(database ID 91) in 60 second(s) (analysis 7289 ms, redo 96349 ms, undo 107ms.) This is an informational message only. No user action is required

The text highlighted in blue explains the three phases of database recovery as explained earlier.

Also if you want to check how much RECOVERY is done for a database; you can use the following command to Read SQL Server Log File:

EXEC master.dbo.xp_readerrorlog 0, 1, N'Recovery of database', N'YourDatabaseName', NULL, NULL, N'desc'

After reading the SQL Server Error Logs; if you think your database is taking longer time to recover then you can try the following command to bring your database Online:

RESTORE DATABASE YourDatabaseName WITH RECOVERY

After executing above command (once or twice ) ; you can check the status of your database from the view: sys.databases and it should be online.

Possible causes for the databases to stuck in “In Recovery” mode?

  • Huge size of transaction log file.
  • SQL restarted during a long running transaction.
  • Huge number of VLFs (i.e. virtual Log Files).
  • Could be a bug in SQL Server which is fixed with the help of some patches.