The Recovery Process

What is Recovery ?

The term Recovery process guarantees that all completed transactions in the log are reflected in the data and all incompleted transactions in the log are rolled back. During Recovery SQL Server checks the last checkpoint record in the log. Only the changes that occured since the last checkpoint record need to be examined to determine the transactions to be roll forward or rolled back.

When Recovery Occurs :

Recovery is an Automatic process when SQL Service Instance is Started, when you are restoring a database by using backup, Attaching a Database,Creating Database Snapshot, During Database Mirroring, when  failing over to a Database Mirror.

The Recovery Process algorithm has three phases.

1. Analysis phase—SQL Server reads forward from the last checkpoint record in the

transaction log. This pass identifies a list of pages (the dirty page table [DPT]) that

might have been dirty at the time of the system crash or when SQL Server was shut

down, as well as a list of the uncommitted transactions at the time of the crash.

2. Redo (roll-forward) phase—During this phase, SQL Server rolls forward all the

committed transactions recorded in the log since the last checkpoint. This phase

returns the database to the state it was in at the time of the crash. The starting point

for the redo pass is the LSN of the oldest committed transaction within the DPT, so

that only changes that were not previously check pointed (only the committed dirty

pages) are reapplied.

3. Undo (rollback) phase—This phase moves backward from the end of the log to the

oldest active transaction at the time of the system crash or shutdown. All transactions that were not committed at the time of the crash but that had pages written to the database are undone so that none of their changes are actually reflected in the database.

How to see If a transaction is rolled forward or rolled back:

You can see always in Errorlog.