事务日志 （SQL Server）

  • 一、背景
  • 二、事务日志支持的操作
    • 2.1、单个事务恢复
    • 2.2、在启动 SQL Server 时恢复所有未完成的事务
    • 2.3、将还原的数据库、文件、文件组或页前滚到故障点
    • 2.4、支持事务复制
    • 2.5、支持高可用性和灾难恢复解决方案
  • 三、事务日志特征
  • 四、事务日志截断
  • 五、管理事务日志文件的大小
    • 5.1、监视日志空间使用情况
    • 5.2、缩小日志文件大小
    • 5.3、添加或放大日志文件
    • 5.4、优化 tempdb 事务日志大小
    • 5.5、控制事务日志文件的增长
  • 总结

一、背景

每个 SQL Server 数据库都有一个事务日志，用于记录所有事务以及每个事务所做的数据库修改。

事务日志是数据库的关键组件。如果出现系统故障，您将需要该日志才能使数据库恢复到一致状态。

二、事务日志支持的操作

事务日志支持以下操作：

  • 单个事务恢复。
  • 在启动 SQL Server 时恢复所有未完成的事务。
  • 将还原的数据库、文件、文件组或页前滚到故障点。
  • 支持事务复制。
  • 支持高可用性和灾难恢复解决方案：Always On 可用性组、数据库镜像和日志传送。

2.1、单个事务恢复

如果应用程序发出语句，或者数据库引擎检测到错误（如与客户端的通信丢失），则日志记录用于回滚未完成事务所做的修改。

2.2、在启动 SQL Server 时恢复所有未完成的事务

如果服务器发生故障，数据库可能会处于从未从缓冲区缓存写入数据文件的某些修改的状态，并且数据文件中未完成的事务可能会进行一些修改。启动 SQL Server 实例时，它会运行每个数据库的恢复。日志中记录的可能尚未写入数据文件的每个修改都将前滚。然后回滚在事务日志中找到的每个未完成的事务，以确保保留数据库的完整性。

2.3、将还原的数据库、文件、文件组或页前滚到故障点

在影响数据库文件的硬件丢失或磁盘故障之后，可以将数据库还原到故障点。首先还原上一个完整数据库备份和最后一个差异数据库备份，然后将事务日志备份的后续序列还原到故障点。

还原每个日志备份时，数据库引擎会重新应用日志中记录的所有修改以前滚所有事务。还原上次日志备份后，数据库引擎将使用日志信息回滚此时未完成的所有事务。

2.4、支持事务复制

日志读取器代理监视为事务复制配置的每个数据库的事务日志，并将标记为复制的事务从事务日志复制到分发数据库中。

2.5、支持高可用性和灾难恢复解决方案

备用服务器解决方案、AlwaysOn 可用性组、数据库镜像和日志传送严重依赖事务日志。

在 Always On 可用性组方案中，对数据库（主副本）的每次更新都会立即在数据库的单独完整副本（辅助副本）中重现。主要副本将每个日志记录立即发送到次要副本，次要副本将传入的日志记录应用于可用性组数据库，并不断向前滚动

在日志传送方案中，主服务器将主数据库的事务日志备份发送到一个或多个目标。每个辅助服务器将日志备份还原到其本地辅助数据库。

在数据库镜像方案中，对数据库（主体数据库）的每次更新都会立即在数据库（镜像数据库）的单独完整副本中重现。主体服务器实例将每个日志记录立即发送到镜像服务器实例，镜像服务器实例将传入的日志记录应用于镜像数据库，并不断前滚。

三、事务日志特征

SQL Server 数据库引擎事务日志的特征：

  • 事务日志作为数据库中的单独文件或一组文件实现。日志缓存与数据页的缓冲区缓存分开管理，从而在 SQL Server 数据库引擎中生成简单、快速且可靠的代码。

  • 日志记录和页面的格式不受数据页面格式的限制。

  • 事务日志可以在多个文件中实现。可以通过设置日志的值将文件定义为自动扩展。这减少了事务日志中空间不足的可能性，同时减少了管理开销。有关详细信息，请参阅更改数据库 （事务处理 SQL） 文件和文件组选项。

  • 重用日志文件中的空间的机制非常快速，对事务吞吐量的影响最小。

四、事务日志截断

日志截断可释放日志文件中的空间，以供事务日志重用。您必须定期截断事务日志，以防止它填满分配的空间。有几个因素可能会延迟日志截断，因此监视日志大小很重要。可以最少记录某些操作，以减少它们对事务日志大小的影响。

日志截断从 SQL Server 数据库的逻辑事务日志中删除非活动虚拟日志文件 （VLF），从而释放逻辑日志中的空间以供物理事务日志重用。如果事务日志从未被截断，它最终将填满分配给物理日志文件的所有磁盘空间。

为避免空间不足，除非日志截断由于某种原因而延迟，否则在发生以下事件后会自动截断：

  • 在简单恢复模式下，在检查点之后。
  • 在完整恢复模式或大容量日志恢复模式下，如果自上次备份以来发生了检查点，则在日志备份之后会发生截断（除非它是仅复制日志备份）。
  • 首次使用 FULL 恢复模式创建数据库时，事务日志将根据需要重复使用（类似于 SIMPLE 恢复数据库），直到创建完整数据库备份为止。

五、管理事务日志文件的大小

监视 SQL Server 事务日志大小、收缩事务日志、添加或放大事务日志文件、优化 tempdb 事务日志增长率以及控制事务日志文件的增长。

5.1、监视日志空间使用情况

使用 sys.dm_db_log_space_usage 监视日志空间使用情况。此 DMV 返回有关当前使用的日志空间量的信息，并指示事务日志何时需要截断。

有关当前日志文件大小、其最大大小和文件的自动增长选项的信息，您还可以在 sys.database_files 中使用该日志文件的大小、max_size和增长列。

5.2、缩小日志文件大小

若要减小物理日志文件的物理大小，必须收缩日志文件。当您知道事务日志文件包含未使用的空间时，这很有用。您只能在数据库处于联机状态时收缩日志文件，并且至少有一个虚拟日志文件 （VLF） 可用。在某些情况下，在下一次日志截断之前可能无法收缩日志。

收缩日志文件会删除一个或多个不保存逻辑日志部分的 VLF（即非活动 VLF）。收缩事务日志文件时，将从日志文件末尾删除非活动 VLF，以将日志减小到大约目标大小。

（1）收缩日志文件（不收缩数据库文件）。

  • DBCC SHRINKFILE （Transact-SQL）。

  • 收缩文件。

（2）监视日志文件收缩事件。

  • 日志文件自动收缩事件类。

（3）监视日志空间。

  • sys.dm_db_log_space_usage。

  • sys.database_files。

5.3、添加或放大日志文件

可以通过放大现有日志文件（如果磁盘空间允许）或将日志文件添加到数据库（通常在其他磁盘上）来获取空间。一个事务日志文件就足够了，除非日志空间即将用完，并且保存日志文件的卷上的磁盘空间也已用完。

若要将日志文件添加到数据库，请使用语句的子句。添加日志文件允许日志增长。ADD LOG FILEALTER DATABASE
若要放大日志文件，请使用语句的子句，指定 和 语法。有关详细信息，请参阅更改数据库 （事务处理 SQL） 文件和文件组选项。

5.4、优化 tempdb 事务日志大小

重新启动服务器实例会将 tempdb 数据库的事务日志大小调整为其原始的自动增长前大小。这会降低 tempdb 事务日志的性能。

可以通过在启动或重新启动服务器实例后增加 tempdb 事务日志的大小来避免此开销。

5.5、控制事务日志文件的增长

使用 ALTER 数据库 （Transact-SQL） 文件和文件组选项语句来管理事务日志文件的增长。请注意以下几点：

  • 若要更改当前文件大小（以 KB、MB、GB 和 TB 为单位），请使用该SIZE选项。
  • 要更改增长增量，请使用该FILEGROWTH选项。值为 0 表示自动增长设置为关闭，不允许有额外的空间。
  • 若要控制日志文件的最大大小（以 KB、MB、GB 和 TB 为单位），或将增长设置为“无限制”，请使用该MAXSIZE选项。

总结

  1. 避免日志磁盘过载；确保日志存储能够承受事务负载的 IOPS 和低延迟要求。
  2. 长时间运行的事务等因素使 VLF 长时间保持活动状态，可以限制日志收缩，甚至根本阻止日志收缩。
  3. 在收缩事务日志之前，请记住可能延迟日志截断的因素。如果在日志收缩后再次需要存储空间，事务日志将再次增长，这样做会在日志增长操作期间引入性能开销。
  4. 事务日志的自动增长（自动增长）增量（由选项设置）必须足够大，才能领先于工作负荷事务的需求。
  5. 在数据库中具有多个日志文件不会以任何方式提高性能，因为事务日志文件不像同一文件组中的数据文件那样使用比例填充。