SQL Server Recovery process

During the recovery process, only those changes that have occurred since the last checkpoint are parsed to determine if a redo or revocation is required.

Actions done before the last checkpoint are accurately reflected in the data file, and the recovery process does not need to do anything else.

First stage:

Analysis

This stage constructs a dirty page table, and also constructs an uncommitted activity transaction table.

Phase II:

Redo,

Start with the oldest open active transaction, which will get the necessary locks.

Phase III:

Rollback,

Any transactions that are not committed when SQL Server is stopped are revoked.

--------------------------------------------------------------------------------------------------------------- ----------

LSN of the data page header:

It reflects the last transaction that modifies the data page.

SQL Server Recovery process