SQL Server Database Stuck in “In Recovery” Mode After Restart

SQL Server

Sometimes bad things happen to good people, and sometimes your databases will have issues that you need to correct. When SQL Server restarts, all the databases go through a recovery process before they are available. This is when your the database has to come back online in a consistent state. There are three phases to this process: Analysis, Redo, and Undo.

  • Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is also built for the uncommitted transactions at the time of the SQL Server stopped.
  • Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward pass being the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labeled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, re-doing the logged operations starting back at the oldest open transaction so that the neccessary locks can be aquired.
  • Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.

What could happen is there might be an error that causes this process to stop without finishing, so the database remains in a status of “In Recovery”, which is a serious problem. The first thing you need to check is the error logs. You can get to the error logs via the SSMS GUI, or by manually opening the error log files stored on the server. To find the location of the error log files you can run the command:

sp_readerrorlog

This will output information about the server, including the path to the log files.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s