BizSmooth——企顺学校博客

简介

    SQL Server中的事务日志无疑是SQL Server中最重要的部分之一。因为SQL SERVER利用事务日志来确保持久性(Durability)和事务回滚(Rollback)。从而还部分确保了事务的ACID属性.在SQL Server崩溃时，DBA还可以通过事务日志将数据恢复到指定的时间点。当SQL Server运转良好时，多了解一些事务日志的原理和概念显得并不是那么重要。但是，一旦SQL SERVER发生崩溃时，了解事务日志的原理和概念对于快速做出正确的决策来恢复数据显得尤为重要.本系列文章将会从事务日志的概念，原理，SQL Server如何使用日志来确保持久性属性等方面来谈SQL Server的事务日志.

事务日志的物理组织构架

    事务日志仅仅是记录与其对应数据库上的事务行为和对数据库修改的日志文件.在你新建数据库时，伴随着数据库文件，会有一个默认以ldf为扩展名的事务日志文件. 当然，一个数据库也可以配有多个日志文件，但是在逻辑上，他们可以看成一个.

    在SQL Server对于日志文件的管理，是将逻辑上一个ldf文件划分成多个逻辑上的虚拟日志文件(virtual log files,简称VLFs).以便于管理。用个类比方法来看，日志文件(ldf)好比一趟火车，每一节车厢都是一个虚拟日志文件(VLFs):
    那为什么SQL Server要把日志文件划分出多个VLFS呢？因为SQL Server通过这种方式使得存储引擎管理事务日志更加有效.并且对于日志空间的重复利用也会更加高效。使用VLF作为收缩数据库的最小单位比使用ldf文件作为最小单位无疑是更加高效的.

    VLFS的个数和大小无法通过配置进行设定,而是由SQL Server进行管理.当Create或Alter数据库时,SQL Server通过ldf文件的大小来决定VLFS的大小和数量。在日志文件增长时，SQL Server也会重新规划VLFS的数量.

    注意：根据这个原理不难看书，如果设置日志文件的增量过小，则会产生过多的VLFS,也就是日志文件碎片，过多的日志文件碎片会拖累SQL Server性能.

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

  下面我们来看一个例子:

   创建数据库，指定日志大小为65M

     通过DBCC，我们可以看到，对应的有8个VLFs:

      再次创建数据库，指定日志初始大小为28M:

       可以看到，对应的，VLF的数量变为4:

        而对于日志文件的增长，SQL Server使用了和创建数据库时相同的公式，也就是每次增长比如为2M，则按照公式每次增长4个VLFs.

      我们创建一个TestGrow数据库，指定日志文件为2M，此时有4个VLFS:

         当我们增长2M时，这个2M则是按照公式，再次分配4个VLFs:

      此时，这时能看到的VLFs数量应该为4+4=8个:

       由此可以看出，指定合适的日志文件初始大小和增长，是减少日志碎片最关键的部分.

事务日志的逻辑组织构架

    当针对数据库对象所做的任何修改保存到数据库之前，相应的日志首先会被记录到日志文件。这个记录会被按照先后顺序记录到日志文件的逻辑末尾，并分配一个全局唯一的日志序列号(log sequence number,简称LSN）,这个序列号完全是按照顺序来的，如果日志中两个序列号LSN2>LSN1,则说明LSN2所在LSN1之后发生的.

    由此可以看出，将日志文件分为多个文件除了磁盘空间的考虑之外。完全不会像数据那样可以并行访问，所以将日志文件分为多个完全不会有性能上的提升.

    LSN号可以看作是将日志文件和其记录数据之间的纽带.每一条日志不仅有LSN号，还有其对应事务的事务日志:

 
   许多类型的操作都记录在事务日志中。这些操作包括：
•
每个事务的开始和结束。

•
每次数据修改（插入、更新或删除）。这包括系统存储过程或数据定义语言 (DDL) 语句对包括系统表在内的任何表所做的更改。

•
每次分配或释放区和页。

•
创建或删除表或索引。

   对于LSN如何在ROLLBACK或者是ROLL FORWARD中以及在备份恢复过程中起作用，会在后续文章中提到

总结

    本篇文章从事务日志的逻辑和物理构架简单介绍了事务日志的构成.这是理解SQL Server如何利用日志保证持久性和数据备份恢复的基础。

恢复模型

事务日志是SQL Server的一种内部机制，用来保存所有事务的日志，以及每个事务做的数据库修改。这些日志在操作发生时以字符串日志记录的形式顺序记录。这项功能很重要，如果发生了系统故障可以用此功能将数据库回滚到某个一致的状态。但是，事务日志会一直增长的，我们怎么控制其大小呢？

恢复模型是控制和管理事务日志文件的一种机制。恢复模型控制事务日志如何记录，日志是否自动截断，日志是否需要或者允许备份，哪种恢复操作是允许的。

SQLServer中的每个数据库都有 “恢复模型（Recovery Model）”的属性，它提供三种设置：简单恢复模式，批量恢复模式和完整恢复模式，我们可以根据性能、存储空间和保护数据防丢失的不同需求选择设置。你需要评估你的大批量操作（创建索引或者大批量数据加载）的性能，事务日志存储需求的存储空间，数据丢失的可能性（提交事务丢失），备份和恢复操作的简单程度。数据库可以随时切换到另一种设置以满足业务需求，但是在操作设置之前请评估一下可能产生的影响。

因需求不同，你可能需要不只一种恢复模型。例如，考虑你有一项关键任务的OLTP数据库，为了能在任何时点恢复它（这样就不会有提交的事务会丢失），你应该使用完整恢复模式。但是在你执行一些大操作时（例如，创建索引，SELECT INTO，INSERT SELECT，BCP，BULKINSERT）你可以切换到批量日志恢复模式，这样可以最小化日志量，在完成该操作之后再设置为完整恢复模式。这样可以降低在大批量操作过程中占用掉可用事务日志空间的可能性。

完整恢复模式

正如其名，完整恢复模式对每一个事务都记录日志，并维护这些日志直到做过了日志备份。在这种恢复模式下，你可以设计灾备计划，其中包括一组全备份（或者差异备份）和事务日志备份。要控制事务日志的大小，你需要做事务日志备份，这样它就可以截断处理了。

在完整恢复模式下，你可以利用事务日志备份恢复到任意时间点，因此任何数据都不会丢失，也不会有损坏的数据文件。

优点：完整恢复模式提供了避免数据丢失的全面保护。在最不幸的灾难或者应用程序和用户错误情况下，你可以利用可用的事务日志备份恢复到任何时间点，前提是你的事务日志备份在那个时间点已经完成了。

缺点：在完整恢复模式下，你需要设置定期的事务日志备份，确保事务日志增长可控。否则它会一直增长直到你做下一次全备份。另外，如果事务日志损坏了，那么在最近的事务日志备份之后的变化必须重做。

何时采用：完整恢复模式推荐用于OLTP数据库，这种场景下有很多短事务，你不想丢失提交事务的数据。SQL Server中也有其它一些选项功能（比如：AlwaysOn，数据库镜像，日志迁移，事务复制，变化数据捕获）需要启用完整恢复模式才可用。

批量日志恢复模式

批量日志恢复模式与完整恢复模式类似，都预期会有大批量的数据修改操作（例如，创建索引，SELECT INTO，INSERT SELECT，BCP，BULKINSERT），在这种情况下可以最小化日志记录量，因此它降低了性能影响。但是同时代价就是你可能不能做任何时点的恢复了。作为一种推荐的实践，批量日志恢复模式可以与完整恢复模式一起使用，例如，你通常应该在常规操作时设置为完整恢复模式，然后在偶尔发生大批量操作时临时切换到批量日志恢复模式。最后在完成大批量操作以后，再回到完整恢复模式。如果时间点恢复很重要的话，我们非常推荐在切换回到完整恢复模式以后做一次事务日志备份。

与完整恢复模式类似，事务日志文件将会持续增长，因此你需要频繁做事务日志备份。如果没有大批量操作，批量日志模式与完整恢复模式是一样的，你可以恢复到任何时点，只要事务日志包含对数据库后续做的所有变更记录。

优点：通过对一些事务做最小化日志记录优化大批量操作的性能。不会让事务日志由于这些大批量数据操作而显著增长。

缺点：如果日志损坏，或者在最近日志备份之后发生大批量数据操作，存在数据丢失的可能性。因此自最后一次备份后的变化必须被重做。

何时采用：推荐在偶尔发生的大批量数据操作之前切换到批量日志恢复模式，然后在完成大批量数据操作之后切换回到完整恢复模式。采用这种方式你仍然可以恢复到任何时间点，只是你最后一次事务日志备份不包含大批量数据操作，同时可以将大批量数据操作的日志量最小化。

要注意的是，最小化日志记录意味着只记录恢复事务需要的信息，而不支持时间点恢复。在最小化日志的情况下，事务日志基于大批量变更映射（MCP）页做的大批量数据变更记录页轨迹，而不是对每次变化做日志。这种方式数据库日志会更小，但是在你备份事务日志时，它包括了所有变更页，因此即使事务日志非常小，事务日志备份也可能比它更大。

简单恢复模式

简单恢复模式是所有这几种中最简单的。它只维护最小量信息的SQL Server事务日志文件。SQL Server自己截断事务日志文件，并删除已经达到事务检查点的信息有关的事务。这样空间就可以被重用，完全没有用于灾备目的的事务日志。也就是说，在简单恢复模式下，数据只有最近的全备份或者差异备份中的内容是可恢复的（不支持事务日志备份）。在简单恢复模式下，事务日志会在检查点之后或者在你把数据库切换到简单恢复模式的瞬间马上截断。

用简单恢复模式管理数据库更容易，但是代价是如果数据文件损坏了会丢失更多数据。你只能从最近的全备份或者差异备份中恢复数据；也就是说你会丢失在上一次全备份或者差异备份之后到故障发生之时的所有变化数据。因此，如果你的数据库采用简单恢复模式，你应该保证备份间隔足够长以免影响正常生产运营，同时还要保证备份周期足够短，以免数据丢失太多。

优点：简单恢复模式的可管理性更容易，不需要做事务备份。它只占用检查点事务的空间，确保事务日志文件的增长得以控制。大批量日志操作执行会更优，因为日志最小化了，而且事务日志占用空间也最小。

缺点：你会丢失最后一次全备份或者差异备份之后到故障发生时的所有数据变更，再做恢复就不可能了。

何时采用：在数据仓库的应用场景下，大部分时候做的可能都是在数据加载时做大批量操作，如果遇到故障数据可以从数据源重新生成。你还可以在开发环境或者测试环境使用简单恢复模式，这样事务日志文件增长就可以得到控制。