SQL Server Data Base Recovery Process
Sql Server is having 4 recovery phases :
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.
Note: If VLF's are huge in log file data base may went to "InRecovery" state after rebooting the server, because sql server will spend more time in discovery phase i.e., it need to scan all the VLF's present in the log file.
To check VLF's present in the log file use DBCC LOGINFO.
To avoid such kind of issues we need to set log file auto growth properly based on the log file size.
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.
OR
Starts at the last checkpoint in transaction log. This phase 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
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.
OR
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.
Undo Phase:
Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 2 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.
This marks the end of 4 phase recovery process!
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.
Note: If VLF's are huge in log file data base may went to "InRecovery" state after rebooting the server, because sql server will spend more time in discovery phase i.e., it need to scan all the VLF's present in the log file.
To check VLF's present in the log file use DBCC LOGINFO.
To avoid such kind of issues we need to set log file auto growth properly based on the log file size.
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.
OR
Starts at the last checkpoint in transaction log. This phase 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
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.
OR
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.
Undo Phase:
Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 2 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.
This marks the end of 4 phase recovery process!
