FOUR PHASE SQL SERVER DATABASE RECOVERY PROCESS

Following the data copy phase involving copying of all the data, log, and index-pages from the backup media of a database to the database files, four consecutive phases that take place during SQL Server recovery are

1.       Discovery – is to find the logical structure of the Transaction log file.

2.       Analysis – is to find the best LSN starting from which rolling forward can be done during redo phase.

3.       Redo – is the phase during which the changes caused by active transactions (at the time of crash) are hardened onto Data files.

4.       Undo – is the phase where in, rolling back of the active transactions for consistency, takes place.

Discovery Phase:

The first phase of recovering a database is called discovery where all the VLFs are scanned (in serial and single threaded fashion) before actual recovery starts. Since this happens much before the analysis phase, there are no messages indicating the progress in the SQL Server error log. Depending on the number of VLFs this initial discovery phase can take several hours even if there are no transactions in the log that need to be processed.

This is the reason why it is preferred to have optimal number of VLFs in a log file.

For further information on influence of VLFs in Transaction Log throughput please refer point #8 in Kimberly Trip’s blog http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Analysis Phase:

Preparation of ‘Dirty Page Table’ (DPT) and ‘Active Transaction Table’ (ATT) are the prime motives of Analysis phase. These two tables are put to use by SQL Server during subsequent redo and undo phases respectively.

To create DPT, SQL Server requires to make a note all the pages and their LSNs that might have been dirty (à not yet hardened) at the time of crash, from the transaction log (.ldf), so that during redo phase all such pages will be rolled forward and at the end of redo phase the database would be in such a state as if it was just before crash.

As all the pages prior to last checkpoint would have been already hardened and the pages after the last checkpoint are the ones that are dirty but yet to get hardened. Hence analysis phase starts (in the sense SQL Server starts reading using the .ldf) from the last checkpoint LSN till end of transaction log.

Scanning through the transaction log from the latest checkpoint till end of transaction log prepares the list of all pages that are dirty and obviously not hardened as they are after checkpoint. This list is the DPT. The minimum of all the LSNs available from DPT will be the minimum recovery LSN. Similarly using transaction log file, active transaction table is generated.

Redo Phase:

“Rolling forward all the changes that took place after the checkpoint and just before the crash so that at the end of redo phase the db would be in a state as if it was just before the crash”, is the intent of redo phase.

Hence making use of minimum recovery LSN obtained from DPT, starting from the minimum recovery LSN and till the LSN at end of transaction log, SQL server rolls forward (hardens) all the changes that are present in all the dirty ( not yet hardened) pages and brings the db to the desired state.

Undo Phase:

Ensuring that the data integrity is not hampered so that db can be opened for access is the aim of Undo phase. For this, all the changes made by all the transactions that were active at the time of crash are to be rolled back.

Hence, SQL server, using ATT , starting from LSN at the end of transaction log will rollback all the changes caused by all the active transactions till the LSN of beginning of oldest transaction(among the active transactions present in ATT), which is available from transaction log and opens the database for user access.

This marks the end of 4 phase recovery process!

REFERENCES

1. Chapter 3 from “Inside Microsoft SQL Server 2000” by Kalen Delaney

2. http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx