Instance Recovery in SQL Server Explained

instancerecoverysql server

When the instance crashes due to unexpected disaster in SQL Server, how does the SQL Server recover the databases?

SQL Server goes through which phases to recover the data and which are the steps that are performed in the instance recovery by SQL Server?

Best Answer

(Copied from https://stackoverflow.com/questions/2391399/three-phases-of-recovery-the-analysis-phase-the-redo-phase-and-finally-the-u)

You might find this document interesting : Simple overview of the SQL Server Recovery Process

Phases of recovery

the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.

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 built of the uncommitted transactions at the time of the SQL Server stopped also.

Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right 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.

Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).

Related Question