SQL Server中的事务日志管理(2/9)：事务日志架构概述

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

当一切正常时，没有必要特别留意什么是事务日志，它是如何工作的。你只要确保每个数据库都有正确的备份。当出现问题时，事务日志的理解对于采取修正操作是重要的，尤其在需要紧急恢复数据库到指定点时。这系列文章会告诉你每个DBA应该知道的具体细节。

虽然我们想回避它，去讨论下事务日志的内部结构和内部运行机制，但适当的理解下日志维护技术是有益的。这个话题在Paul Randal的讲座《理解SQL Server里的日志和恢复》里已经讲得很透彻，另外Kalen Delaney的《深入解析Microsoft SQL Server 2008》书里也有很详细的讲解，因此在这里我们将会简单介绍。

虚拟日志文件（Vitual Log Files（VLF））

事务日志文件是连续的文件；换句话说，SQL Server是连续写事务日志的（不像数据文件，它是随机写入的，因为数据是在随机的数据页里修改的）。

存储注意事项

在写入数据和日志文件的不同方式意味着它们也会有不同的存储注意事项，例如对于存储各个文件类型的硬盘，要配置合适的RAID，这个会在以后的文章里介绍。

每个插入日志文件的日志记录会用 逻辑序列号（Logical Sequence Number（LSN）） 标记。当数据库和它关联的日志文件第一次创建时，第1条日志记录标志着逻辑文件的开始，同时也是物理文件的开始。LSN接下来也是自增长；最近增加的日志记录总会有最大的LSN，也标志着逻辑文件的结束（等下会详细讲解）。所有日志记录与提供的事务在 LSN链（LSN chain） 里链接，LSN上有指针前后指向当前操作的成功的和进行中的事务操作。

在内部，SQL Server把事务日志文件分成许多所谓的 虚拟日志文件（virtual log files(VLFs）） 段。图1描述了8个VLFs组成的事务日志，还有标记了日志的活动部分。

2.1 有8个VLFs的日志记录

在第1篇文章里我们提到，任何与打开事务相关日志记录都需要回滚的可能。另外，在数据库里还有很多其他使用事务日志的活动（包括复制，镜像和修改数据快照），也需要保持事务日志记录直到这些活动已经处理。在图2.1里显示的最小LSN的日志记录，是定义为“用作成功的数据库范围回滚或数据库里其他活动和操作的最老记录“。有时候它们也称为日志“头”。

活动日志记录的原因

活动事务可以让日志记录活动，除此之外，还有很多原因让它活动。这个在接下来的文章都会谈到。

现在可以说，如果一条日志记录对于任何事务或活动需要的话，这个日志记录就是活动的，它对应的VLF也是活动的一部分。

如图2.1所示，最近的日志记录总有 最大LSN ，这标志着日志的逻辑尾。接下来的记录都会写在日志逻辑尾。在 最小LSN 和和 最大LSN 之间的文件部分称为 活动日志（active log） 。

活动日志不包含“活动”（例如打开）事务的细节，这很重要。例如，假设有个开始于上午9点，持续30分钟执行的打开事务（T1）日志记录定义了最小LSN。如果接下来的事务（T2）开始于上午9点10分，结束于9点11分，它还会是活动日志的一部分，因为相关日志记录的LSN比最小LSN大。在上午9点30分，当T1提交时，对于开始于上午9点25分的打开事务（T3）的日志记录会有新的最小LSN。到这时，对于T2的日志记录不会是活动日志的一部分。

任何包含活动日志任何部分的VLF都被认为是活动VLF。例如，图2.1的VLF3就是个活动VLF，即使它包含的大部分日志记录不是活动日志的一部分。一旦事务开始并提交，我们可以（简单）想象出日志头会在图2.1里从左往右移动，因此刚才包含活动记录部分的VLFS现在变成了不活动（VLF1和VLF2），刚才没用到的（VLF8）的VLFS会变成活动日志部分。

标记一个VLF为“不活动”具体做什么取决于数据库使用的恢复模式，接下来我们会谈到。

日志截断与空间重用

这里要注意的重点是日志文件里截断的最小单位不是各个日志记录或日志块，是VLF。如果在VLF里的一条日志记录还是活动日志部分，那么整个VLF被认为是活动的且 不能被截断（cannot be truncated） 。

一把来说，一个VLF会是2个物理状态的1个：活动（active）或不活动（inactive）。但是，基于VLF的可能不同“行为”，我们可以分出4个逻辑状态：

活动（Active） ——这个状态的VLF是活动的，因为它至少包含活动日志部分的一条记录，因此它需要被回滚或其他目的。
可恢复（Recoverable） ——这个状态的VLF是不活动的，但没被截断或备份，空间不可以重用。
可重用（Reusable） ——这个状态的VLF是不活动的，它已被截断或备份，空间可以重用。
未使用（Unused） ——这个状态的VLF是不活动的，在它里面还没有被记录的日志记录。

把一个VLF标记为不活动——按照我们的逻辑状态，这表示从状态2切换到状态3——被称为日志截断（log truncation）。

这个日志截断什么时候发生取决于使用的恢复模式。当数据库在简单（Simple）恢复模式时，活动的VLF在检查点操作时变成不活动。当检查点发生时，缓存中的任何脏页写回到硬盘，然后日志中的空间变成可重用。

但是，在完整（FULL）或大容量日志（BULL LOGGED）模式里，只有日志备份可以把活动的VLF变成不活动。这样的话，一旦日志备份已完成备份，任何VLFs不在需要是不活动的，因此是可重用的。

在图2.2，我们看看到检查点（或日志备份）的结果，VLF1和VLF2已被截断且是不活动。活动日志的开始现在是VLF3的开始，VLF8还从未用过，因此它是不活动的（状态4）。

2.2 截断后，有8个VLF的事务日志

下一个要考虑的问题，当活动日志到达VLF7尾时会发生什么。日志文件中空间简单来想都是反复被重用的，尽管有让空间重用模式变得相当专制的复杂因素，对此在这个系列文章里我们不会深入探讨。

不过，在最简单的情况下，一旦日志的逻辑尾到达VLF尾，SQL Server会开始重用接下来顺序的不活动VLF。在图2.1，会是VLF8。当VLF8满了，它会回绕重用VLF1和VLF2。如果没有更多的可用VLF，日志会需要自动增长，增加更多的VLF。由于自动增长被停用或磁盘提供的日志文件满了（没磁盘空间了），活动日志的逻辑尾会遇上日志文件的逻辑尾，事务日志满了，就会发生9002错误。

这个架构解释了原因，例如，一个长时间运行的事务，或由于某个原因复制的事务没有发送到派发的数据库，或者是丢失连接的镜像，还有其它的原因，会造成日志增长非常大。例如，考虑下图2.2，关联最小LSN的事务非常长时间运行。日志已经包裹了，填满了VLF1，VLF2和VLF8，没有不活动的VLF了。即使在最小LSN后的每个事务已提交，在这些VLFs没有可重用的空间，因为所有的的VLF还是活动日志部分。

我们很容易演示这个例子。首先，重新执行1.1代码删除和重建TestDB数据库。

 USE master ;

 IF EXISTS ( SELECT  name

             FROM    sys.databases

             WHERE   name = 'TestDB' )

     DROP DATABASE TestDB ;

 CREATE DATABASE TestDB ON

 (

   NAME = TestDB_dat,

   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.mdf'

 ) LOG ON

 (

   NAME = TestDB_log,

   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.ldf'

 ) ;

 DBCC SQLPERF(LOGSPACE) ;

然后创建一个小表，在显性事务里更新表里的行，把事务停留在打开状态（不要提交它）。

 USE TestDB

 CREATE TABLE Test

  (

     num INT,

      Filler CHAR(8000)

  )

  GO

  INSERT INTO dbo.Test

          ( num, Filler )

  VALUES  ( 1, -- num - int

            REPLICATE('A', 8000)  -- Filler - char(8000)

            )           

 BEGIN TRAN

 UPDATE dbo.Test SET Filler=REPLICATE('B',8000) WHERE num=1

 DBCC SQLPERF(LOGSPACE) ;

 --ROLLBACK

在新开会话里，进行数据库完整备份，插入100万条记录后，查看日志空间占用。

 -- full backup of the database

 BACKUP DATABASE TestDB

 TO DISK ='C:\Backups\TestDB.bak'

 WITH INIT;

 GO

 USE TestDB ;

 GO

 IF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL

     DROP TABLE dbo.LogTest ;

 --===== AUTHOR: Jeff Moden

 --===== Create and populate 1,000,000 row test table.

 -- "SomeID" has range of 1 to 1000000 unique numbers

 -- "SomeInt" has range of 1 to 50000 non-unique numbers

 -- "SomeLetters2";"AA"-"ZZ" non-unique 2-char strings

 -- "SomeMoney"; 0.0000 to 99.9999 non-unique numbers

 -- "SomeDate" ; >=01/01/2000 and <01/01/2010 non-unique

 -- "SomeHex12"; 12 random hex characters (ie, 0-9,A-F)

 SELECT TOP 1000000

         SomeID = IDENTITY( INT,1,1 ),

         SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,

         SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)

         + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,

         SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS MONEY) ,

         SomeDate = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 36524.0 AS DATETIME) ,

         SomeHex12 = RIGHT(NEWID(), 12)

 INTO    dbo.LogTest

 FROM    sys.all_columns ac1

         CROSS JOIN sys.all_columns ac2 ;

 DBCC SQLPERF(LOGSPACE) ;

进行日志备份。

 -- now backup the transaction log

 BACKUP LOG TestDB

 TO DISK ='C:\Backups\TestDB_log.bak'

 WITH INIT ;

 GO

 DBCC SQLPERF(LOGSPACE) ;

可以看到，日志备份后，空间不会被重用。

我们把刚才的事务回滚。

 ROLLBACK

再次备份日志。

 -- now backup the transaction log

 BACKUP LOG TestDB

 TO DISK ='C:\Backups\TestDB_log.bak'

 WITH INIT ;

 GO

 DBCC SQLPERF(LOGSPACE) ;

可以看到，日志空间已经被重用了。

在这样的情况下，如果活动日志占用的“区域”很大，大量的空间没被重用，这样的话，日志文件大小会增大（增长并增长……）。可以延迟日志文件截断的其他因素会在 第8篇——救命，我的日志满了 里讨论。

太多虚拟日志文件？

一般来说，SQL Server决定VLF的大小优化和个数分配。但是，频繁自动增长的事务日志，在小增量里会有很大数的小VLF。这个现象称为 日志碎片（log fragmentation） ，我们可以通过第1篇里的代码演示下，同时可以使用DBCC LogInfo命令来询问VLF架构。

使用DBCC LogInfo询问日志信息

DBCC LogInfo是个未公开且不支持的命令——微软对此很少介绍。但是，它可以用来询问VLFs。它每个VLF返回一行，除了别的以外，还有VLF的状态。0值状态表示这个VLF是可用的（在状态3和4里），如上介绍，2值状态表示这个是不可用的（在状态1和2里）。在后续的文章我们会详细介绍它。

我们删除并重建TestDB数据。

 USE master ;

 IF EXISTS ( SELECT  name

             FROM    sys.databases

             WHERE   name = 'TestDB' )

     DROP DATABASE TestDB ;

 CREATE DATABASE TestDB ON

 (

   NAME = TestDB_dat,

   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.mdf'

 ) LOG ON

 (

   NAME = TestDB_log,

   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.ldf'

 ) ;

然后运行DBCC LogInfo命令。

 -- how many VLFs?

 DBCC Loginfo

 GO

现在我们并没有返回任何有意义的列；4行返回表示我们有4个VLF。现在我们往表里插入100条记录到TetsDB数据库的LogTest表，再次执行DBCC LogInfo命令。

 USE TestDB ;

 GO

 IF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL

     DROP TABLE dbo.LogTest ;

 --===== AUTHOR: Jeff Moden

 --===== Create and populate 1,000,000 row test table.

 -- "SomeID" has range of 1 to 1000000 unique numbers

 -- "SomeInt" has range of 1 to 50000 non-unique numbers

 -- "SomeLetters2";"AA"-"ZZ" non-unique 2-char strings

 -- "SomeMoney"; 0.0000 to 99.9999 non-unique numbers

 -- "SomeDate" ; >=01/01/2000 and <01/01/2010 non-unique

 -- "SomeHex12"; 12 random hex characters (ie, 0-9,A-F)

 SELECT TOP 1000000

         SomeID = IDENTITY( INT,1,1 ),

         SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,

         SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)

         + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,

         SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS MONEY) ,

         SomeDate = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 36524.0 AS DATETIME) ,

         SomeHex12 = RIGHT(NEWID(), 12)

 INTO    dbo.LogTest

 FROM    sys.all_columns ac1

         CROSS JOIN sys.all_columns ac2 ;

 USE TestDB

 -- how many VLFs?

 DBCC Loginfo

 GO

现在我们有131行返回，这表示我们有131个VLF！来自model数据库的增长属性决定日志文件有非常小的初始大小，然后在相对小的增量里增长。对于数据库对象，对于这里活动，这些属性是不合适的，会导致大量的VLF。

日志文件碎片对性能会造成很大的影响，尤其在灾难恢复，还原，日志备份；换句话说，这些操作会读日志文件。我们会在第7篇——事务日志的大小和增长详细讨论它，还会展示如何通过修正日志文件的大小来避免碎片。但是，为了给你它会有的影响的大致情况，Lichi Shea已经演示了：但与16个VLF的数据库和20000个VLF的数据相比，在进行数据修改的性能上会带来很大的影响。

最后，在一个日志文件里VLF的合理个数问题取决于日志的大小。一般来说，微软认为超过200个VLF就要关注它造成问题的可能，但在很大的日志文件里（例如500GB）只有200个VLF也会是个问题，这些VLF太大，限制令空间重用。Kimberly Tripp的“事务日志VLF——太多还是太少”详细讨论了这个问题。