Transaction Log Physical Architecture

官方原文：

http://msdn.microsoft.com/en-us/library/ms179355(SQL.90).aspx

The transaction log in a database maps over one or more physical files. Conceptually, the log file is a string of log records（一串日志记录）. Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log.

The SQL Server Database Engine divides each physical log file internally into a number of virtual log files（虚拟日志单元）. Virtual log files have no fixed size（没有固定大小）, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment（VLFs的大小取决于已存在的日志文件大小和新增的日志文件大小，如果日志文件新增次数过多，会造成VLFs数量过多，从而影响数据库性能）. The size or number of virtual log files cannot be configured or set by administrators（VLFs的大小和数量是不能配置的，由数据库自动管理。）.

The only time virtual log files affect system performance is if the log files are defined by small size and growth_incrementvalues. If these log files grow to a large size because of many small increments, they will have lots of virtual log files. This can slow down database startup and also log backup and restore operations（在备份和还原到时候，如果VLFs的数量过多就会影响效率，所以建议一开始就设定好日志文件的大小）. We recommend that you assign log files a size value close to the final size required, and also have a relatively large growth_increment value.

The transaction log is a wrap-around file. For example, consider a database with one physical log file divided into four virtual log files. When the database is created, the logical log file begins at the start of the physical log file（事务日志文件在物理上是一个回绕的文件，在逻辑上则是一个线性的文件）. New log records are added at the end of the logical log and expand toward the end of the physical log.

Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback.

数据库将会truncate所有逻辑上在MinLSN之前的VLFs日志，如下图中MinLSN在VLF3中，那么VLF1和VLF2就会被截断。truncate的单位是VLF。

所谓的MinLSN就是最老未提交事务的第一个log record 的LSN。如下图所示，我们发现MinLSN在Last Checkpoint之前，这是因为在checkpoint的时候，还有事务没有提交，那么最老没有提交的事务的其实LSN就是MinLSN，如果我们在checkpoint的时候所有事务都提交了，那么MinLSN就是checkpoint的LSN。

The transaction log in the example database would look similar to the one in the following illustration.

When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.

This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log.只要在逻辑上日志文件没有写满，那么这样的回绕会永远持续下去。只有当日志空间写满了以后，我们需要增加日志文件的空间。

If the old log records are truncated frequently enough to always leave sufficient room for all the new log records created through the next checkpoint, the log never fills. However, if the end of the logical log does reach the start of the logical log, one of two things occurs:

• If the FILEGROWTH setting is enabled for the log and space is available on the disk, the file is extended by the amount specified in growth_increment and the new log records are added to the extension. 如果日志文件允许增长，那么增长日志文件
• If the FILEGROWTH setting is not enabled, or the disk that is holding the log file has less free space than the amount specified in growth_increment, an 9002 error is generated.如果日志文件不允许增长，那么报错。

If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file.

参考中文书籍：

Microsoft SQL Server企业级平台管理实践

数据库的另一种文件类型是日志文件。日志文件包含用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件，当然也可以有多个。日志文件的推荐文件扩展名是.ldf。与数据文件按8 KB组织不同，日志文件不是按page/extent来组织的。

SQL Server数据库引擎在内部将每一物理日志文件分成多个虚拟日志单元(virtual log file)。虚拟日志单元没有固定大小，且一个物理日志文件所包含的虚拟日志单元数不固定。管理员不能配置或设置虚拟日志单元的大小或数量。但SQL Server会尝试控制虚拟日志单元的数目，把它限制在一个合理的范围里。例外情况是，日志文件每自动增长一次，会至少增加一个虚拟日志单元。所以，如果一个日志文件经历了多次小的自动增长，里面的虚拟日志单元数目会比正常的日志文件多很多。这种情况会影响到日志文件管理的效率，甚至造成数据库启动要花很长时间。

事务日志是一种回绕的文件。例如，假设有一个数据库，它包含一个分成5个虚拟日志单元的物理日志文件。当创建数据库时，逻辑日志文件从物理日志文件的始端开始。新日志记录被添加到逻辑日志的末端，然后向物理日志的末端扩张。事务日志的外观与图1-13所示相似。

当逻辑日志的末端到达物理日志文件的末端时，新的日志记录将回绕到物理日志文件的始端，继续向后写。

这点需要注意，回绕形式的日志。貌似日志都是回绕的，oracle也是这样。同一个备份记录，LSN是递增的，但是不同的备份记录，LSN就不一定。

实验

参考：浅谈SQL Server中的事务日志(一)----事务日志的物理和逻辑构架

 SQL Server创建数据库时，根据日志文件(ldf)的大小，生成VLF的数量公式如下:

VLF的四个状态

A VLF can be in one of four states:

• Active The active portion of the log begins at the minimum LSN representing an active (uncommitted) transaction. The active portion of the log ends at the last LSN written. Any VLFs that contain any part of the active log are considered active VLFs. (Unused space in the physical log is not part of any VLF.) Figure 5-3 contains 04 active VLFs. Active的VLF是从包括MinLSN那个VLF开始到最后LSN所在的VLF。

• Recoverable The portion of the log preceding the oldest active transaction is needed only to maintain a sequence of log backups for restoring the database to a former state.

• Reusable If transaction log backups are not being maintained or if you have already backed up the log, VLFs before the oldest active transaction are not needed and can be reused. Truncating or backing up the transaction log will change recoverable VLFs into reusable VLFs. 如果已经对事务日志进行备份，那么从MinLSN之前的日志可以重用。

• Unused One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place or if earlier VLFs have been marked as reusable and then reused. 从始至终没有用过的VLF，如果reuseabe的VLF很多的话，那么就会有Unused，不然只会在开始阶段存在Unused的VLF。