Instance recovery parsing for SQL Server

As with Oracle, SQL Server also makes instance recovery (Instance Recovery) when non-conformance is turned off, and this article describes some of the SQL Server instance recovery knowledge based on the article in the stack overflow.

Original link:https://stackoverflow.com/questions/41932735/sql-server-instance-recovery

about Oracle's instance recovery reference before the blog post: http://www.cnblogs.com/leohahah/p/6973600.html

First look at the role of the transaction log in SQL Server:

In a SQL Server database, the transaction log is used to record the page changes made by the transaction in buffer cache.

When we update some data, the database records the pre-and post-images of the relevant data pages in the transaction log and generates a unique LSN (log seq number) for each transaction, and when the checkpoint occurs, SQL Server ensures that the dirty blocks before the checkpoint LSN are all written to disk. Therefore, the transaction log for SQL Server has both the redo and undo functions.

However, if our database is forced to shut down or the server is unexpectedly powered down, the database will be in a non-uniform state (except for a repository with no business), which means that all transactions after the checkpoint (either committed or uncommitted) have an exception, the committed transaction may not be written to the disk , an uncommitted long transaction may have a portion of the dirty block already written to disk, the database must be in a consistent state to be opened properly, and the instance recovery must be done at this point .

instance recovery for SQL Server is divided into two phases:

1. Roll forward

This phase only processes committed transactions, and according to the checkpoint and transaction log records recorded in the boot page, SQL Server reconstructs the memory dirty blocks after the checkpoint and submits the dirty blocks of committed transactions as normal mechanisms.

Temporarily does not operate on dirty blocks that do not commit transactions.

2. Rollback

This phase handles uncommitted transactions, and SQL Server overwrites the blocks of data involved in uncommitted transactions on the hard disk, based on the changes that were recorded in the transaction log before the Block mirror.

To summarize:

1) The purpose of the instance recovery:

• Writes the dirty blocks of all committed transactions to disk.
• Rolls back the uncommitted transaction.
• Advances the checkpoint to the transaction LSN that has been written to disk.

2) Before the instance crashes:

• Some committed transactions are logged by the transaction log, but the dirty blocks are not written to disk
• Dirty blocks in some uncommitted long transactions have been written to disk
• Some uncommitted transactions whose logs remain in log buffer are not written to the transaction log file on disk.

3) Instance recovery phase:

• All logs for uncommitted transactions in the log buffer are emptied when the power is dropped. (The log of committed transactions is written to the disk transaction log file by default)
• Identifies the previous checkpoint from the boot page as the starting point for instance recovery.
• In the roll forward phase, SQL Server reproduces all dirty blocks based on the records of the transaction log. (either committed or uncommitted) and then writes the dirty blocks of the committed transaction to the disk and temporarily does not operate on the dirty blocks of uncommitted transactions.
• During the rollback phase, SQL Server rolls back all uncommitted transactions based on the pre-image recorded in the transaction log.
• Updates the checkpoint LSN in the boot page and the LSN in the transaction log.

In the above introduction we mentioned the boot page, so what is the boot page?

Each database will have a page that records important information about the database, and only one page is typically The 9th page of the PRIMARY filegroup. We can use the following command to view the information on this page:

DBCC TRACEON (3604); godbcc page (' Test ', 1,9,0) go about DBCC page usage here's an explanation: DBCC page ({' dbname ' | dbid}, FileNum, Pagenum [, PR INTOPT={0|1|2|3}]) The printopt parameter has the following meanings:0-print just the page header1-page header plus PE R-row hex dumps and a dump of the page slot array (unless its a page that doesn ' t has one, like allocation bitmaps) 2-PA GE Header plus Whole page hex dump3-page header plus detailed per-row interpretation

The checkpoint LSN is recorded in the boot page, which is the starting point for instance recovery, and if the page cannot be accessed, the database cannot be attached, opened, or otherwise manipulated. The checkpoint LSN is only recorded in Bootpage, so this is a page that is essential for instance recovery.

For the explanation of checkpoints in SQL Server:

When a checkpoint occurs, regardless of how the checkpoint is triggered (by performing a checkpoint command manually, or if the database performs a differential differential backup, or a checkpoint that is automatically generated by the database), the database does the following:

• All dirty blocks are written to disk, regardless of whether the transaction has been committed.
• Before these dirty blocks are written to disk, all transaction logs about these dirty block changes are also written to disk from log buffer, which ensures the validity and orderliness of instance recovery, which is known as Write-ahead logging (log first), The operation of the log written to the hard disk is strictly time-serialized, and it is not possible to write to the disk in a discrete transaction unit, so the write disk operation of a dirty block may cause some previous, unrelated transaction logs in log buffer to be written to disk. But it is good that the transaction log is always the sooner written to disk the better.
  
• The LSN of the checkpoint is recorded in the database boot page. dbi_checkptlsn area.

 Here you can review some of the Oracle checkpoint mechanism, is also the CKPT process trigger DBWR write dirty block, and if the dirty block to write the SCN greater than LGWR SCN,DBWR will also trigger LGWR to write the dirty block of the relevant log buffer to write to the redo file, Similar to the SQL Server log write-first mechanism.

Instance recovery parsing for SQL Server