Phases of Recovery in Database

At the time of SQL instance reboot or start, database goes in the three phases of recovery...

1. Analysis Phase of recovery

2. Redo Phase of recovery 

3. Undo Phase of recovery  

1. Analysis Phase of recovery

 This phase does two things... 

   First, construct a Dirty Page Table (DPT) 

   Second, construct an Active Transaction Table.

Dirty Page Table consist of pages which might be  dirty (having changes in pages which has down by uncommitted transaction) at the time of DB went down.

Active Transaction Table consists of transactions which were active when this DB shutdown happened.

2. Redo Phase of recovery

This phase return the DB in just before image of the DB when the SQL Engine stopped. 

Starting point of this phase is the oldest uncommitted transaction. The min-LSN in Dirty Page Table 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 necessary locks can be acquired.

3. Undo Phase of recovery 

In this phase, the list of active transaction which where identified 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.