SQL Server Storage Engine-Log

3. SQL Server Storage Engine Log Chapter

(3.1) Log structure

(3.1.1) Physical Log

(0) The physical log is the. ldf file for the database, and when then the prefix is customizable, the default is. ldf

(1) A SQL Server database, you can define multiple physical log files, SQL Servers logically treat them as a whole, sequentially write to log records, run out of the first, and then use the next: the current space of the first log file, if there are no assignable VLF, The next log file's VLF will be used until the last log file has no assignable VLF, and will return to the first log to start growing; the use of VLF such as:

　　　　　　　　　　　　　　　　　　　　　　　　

SQL Server divides the transaction log file into a number of VLF (virtual log files), which is a dummy logfile.

(2) The initial size of the physical log file is at least 512KB

(3) Log files can not be placed in the file group

(3.1.2) Virtual log

(0) log files in addition to the file header page, the other VLF parts are not stored as data pages, the physical log in virtual log (VLF) for the smallest unit of growth, contraction and use, usually the VLF size of 256KB, but the first VLF is the smallest size of 8K, Because the first page of 8K is the header page of the log file.

(1) The virtual log is maintained by SQL Server, varying in size and quantity, can not be manually intervened, but can be assigned a large physical log, or set a large increase in physical log, to reduce the generation of virtual logs, thereby reducing the cost of maintaining virtual log database, and improve database startup, The speed of the backup restore.

(3.1.3) Logical log

(0) Records of database logical operations, each transaction may have multiple log records, each log record is marked by a unique sequential growth LSN, and DBCC log () to view log files such as:

(1) SQL Server uses logging to guarantee the basic properties of transactions and database recovery.

(2) SQL Server database follows the principle of the pre-write log (WAL).

(3.1.4) Activity log

(0) from MinLSN onwards the log section is the activity log. For example, with the earliest active transaction starting point LSN142 as the MinLSN, the log section from 142 onwards is the activity log:

(1) The LSN of the checkpoint LSN, the earliest active transaction start LSN, and the earliest start of the replication transaction that has not been passed to the distribution database, with the minimum value as MINLSN;

　　

(3.2) Log management

(3.2.1) Truncation

(0) SQL Server can truncate the log to achieve a wrapping of the physical log, truncating only the portion of the log that is truncated as reusable, based on the settings of the database recovery model: Simple/bulk_logged/full, in simple mode, SQL The server automatically truncates the log, similar to Oracle's non-archive mode.

(1) In simple mode, Checkpoint automatically truncates the inactive portion of the log, in full and bulk_logged mode, only by logging backups to truncate the log.

(2) SQL Server truncates the log, does not actively release the disk space occupied by the log files, it is necessary to manually shrink the log files will be released, but it is generally not recommended to do so, after all, the time log files grow again need to re-request disk space.

(3) The truncation of the log file is in VLF, from the first VLF of the active log record, to the previous VLF of the VLF where the MinLSN is located, such as:

(4) The Inactive log section can only be truncated.

(5) When running a long transaction and has not ended, this time will affect the minlsn of the advance, and thus affect the truncation of the log file, which will occur, even in simple mode, the log file will become very large, even the disk has eaten all the space, the transaction log full 9002 error.

(3.2.2) Backup

(0) SQL Server does not have an arch process in Oracle, does not automatically archive logs like Oracle, requires manual backups, and cannot back up individual log files when there are multiple physical log files.

(1) When a database fails to recover, the online logs need to be manually backed up via the NO_TRUNCATE option, that is, tail-log backups, and then use a tail-log backup to recover from the previous backup.

(3.2.3) Restore

The restore occurs in two cases, one is when the database restarts, and the first is when the backup set is restored manually;

(0) The process of restoring is to put the data and logs in memory to simulate the user read and write operations. Restoring is only necessary to redo or undo the log portion after the last checkpoint, which is why the checkpoint mechanism improves recovery efficiency.

(1) Redo and undo:

Redo (REDO) on restore if the transaction log has ended (committed or rolled back) and the data page has not been refreshed;

If the transaction log does not end, but the data dirty page has been flushed to disk, rollback (UNDO).

(2) The log record contains the two LSN of the data page before and when modified, and if the LSN of the current data page header equals the pre-modified LSN, the log operation is redo, and if the LSN of the data page header is equal to or greater than the LSN at the time of the modification, the log operation is skipped and no redo is done.

(3) No manual intervention is required for database restart, and manual intervention is required to recover from the backup set. Because it is sometimes necessary to revert to an operation point, all log records are not fully recovered.

　　　

--------------------------------------------------------------------------------------
Translated from: http://qianzhang.blog.51cto.com/317608/1217346
--------------------------------------------------------------------------------------

SQL Server Storage Engine-log