SQL Server事务日志体系结构和管理指南

适用于: SQL Server(所有受支持的版本) Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)

每个 SQL Server 数据库都具有事务日志,用于记录所有事务以及每个事务对数据库所做的修改。 事务日志是数据库的关键组件,如果发生系统故障,可能需要事务日志才能使数据库恢复一致状态。 本指南提供有关事务日志的物理和逻辑体系结构的信息。 了解该体系结构可以提高您在管理事务日志时的效率。

事务日志逻辑体系结构

SQL Server 事务日志按逻辑运行,就好像事务日志是一串日志记录一样。 每个日志记录由 LSN) (日志序列号 标识。 每条新日志记录均写入日志的逻辑结尾处,并使用一个比前面记录的 LSN 更高的 LSN。 日志记录按创建的顺序存储:如果 LSN2 大于 LSN1,则 LSN2 所标识的日志记录描述的更改发生在日志记录 LSN1 描述的更改之后。 每条日志记录都包含其所属事务的 ID。 对于每个事务,与事务相关联的所有日志记录通过使用可提高事务回滚速度的向后指针挨个链接在一个链中。

数据修改的日志记录记录所执行的逻辑操作,或者记录修改数据前后的图像。 之前的映像是执行操作前的数据副本;后映像是执行操作后数据的副本。

操作的恢复步骤取决于日志记录的类型:

  • 记录逻辑操作

    • 若要前滚逻辑操作,请再次执行该操作。
    • 若要回滚逻辑操作,请执行反向逻辑操作。
  • 记录前像和后像

    • 若要前滚操作,请应用映像后的 。
    • 若要回滚操作,请应用映像之前的 。

许多类型的操作都记录在事务日志中。 这些操作包括:

  • 每个事务的开始和结束。

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

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

  • 创建或删除表或索引。

回滚操作也记录在日志中。 每个事务都会在事务日志中保留空间,以确保存在足够的日志空间来支持由显式回滚语句或遇到错误导致的回滚。 保留的空间量取决于在事务中执行的操作,但通常等于用于记录每个操作的空间量。 事务完成后将释放此保留空间。

日志文件中从必须存在以确保数据库范围内成功回滚的第一条日志记录到最后写入的日志记录之间的部分称为日志的活动部分,即“活动日志”或“日志尾部” 。 这是进行数据库完整恢复所需的日志部分。 永远不能截断活动日志的任何部分。 第一条日志记录 (LSN) 的日志序列号称为 MinLSN) (最小恢复 LSN 。 有关事务日志支持的操作的详细信息,请参阅事务日志 (SQL Server)

差异和日志备份将还原的数据库推到稍后的时间,该时间与一个更高的 LSN 相对应。

事务日志物理体系结构

数据库事务日志映射在一个或多个物理文件上。 从概念上讲,日志文件是一系列日志记录。 从物理上讲,日志记录序列被有效地存储在实现事务日志的物理文件集中。 每个数据库必须至少有一个日志文件。

虚拟日志文件 (VLF)

SQL Server数据库引擎在内部将每个物理日志文件划分为多个虚拟日志文件, (VLF) 。 虚拟日志文件没有固定大小,并且物理日志文件没有固定数量的虚拟日志文件。 数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。 数据库引擎尝试维护一些虚拟文件。 在扩展日志文件后,虚拟文件的大小是现有日志大小和新文件增量大小之和。 管理员无法配置或设置虚拟日志文件的大小或数量。

虚拟日志文件 (VLF) 的创建遵循此方法:

  • 在 2014 SQL Server (12.x) 及更高版本中,如果下一个增长小于当前日志物理大小的 1/8,则创建 1 个涵盖增长大小的 VLF。
  • 如果下一个增长超过当前日志大小的 1/8,请使用 2014 年之前的 方法:
    • 如果增长小于 64 MB,请创建 4 个涵盖增长大小的 VLF (例如,对于 1 MB 的增长,) 创建 4 个大小为 256 KB 的 VLF。
      • 在 Azure SQL Database 中,从 SQL Server 2022 (16.x) (所有版本) ,这略有不同。 如果增长小于或等于 64 MB,则数据库引擎仅创建 1 个 VLF 来涵盖增长大小。
    • 如果增长从 64 MB 到 1 GB,请创建 8 个涵盖增长大小的 VLF (例如,对于 512 MB 的增长,) 创建 8 个大小为 64 MB 的 VLF。
    • 如果增长大于 1 GB,请创建 16 个涵盖增长大小的 VLF,例如,对于 8 GB 的增长,创建 16 个大小为 512 MB 的 VLF) 。

如果日志文件以许多小增量增长到较大大小,它们将具有许多虚拟日志文件。 这会降低数据库启动速度以及日志备份和还原操作的速度。 相反,如果将日志文件设置为较大,且只有少量或仅增加一个增量,则它们将具有很少的非常大的虚拟日志文件。 有关正确估计事务日志所需的大小自动增长设置的详细信息,请参阅管理事务日志文件大小的建议部分。

建议使用实现最佳 VLF 分布所需的增量,为日志文件分配 一个接近 所需最终大小的大小值,并且具有相对较大的 growth_increment 值。

请参阅以下提示,确定当前事务日志大小的最佳 VLF 分布:

  • SIZE 参数ALTER DATABASE设置的大小值是日志文件的初始大小。
  • growth_increment值 (也称为) 自动增长值(由 FILEGROWTH 的 参数ALTER DATABASE设置)是每次需要新空间时添加到文件的空间量。

有关 和 参数的详细信息,请参阅 ALTER DATABASE (Transact-SQL) 文件和文件组选项FILEGROWTHALTER DATABASESIZE

提示

若要确定给定实例中所有数据库的当前事务日志大小的最佳 VLF 分布,以及实现所需大小所需的增长增量,请参阅 GitHub 上的 此 Fixing-VLF 脚本

如果 VLF 太多,会发生什么情况?

在数据库恢复过程的初始阶段,SQL Server会发现所有事务日志文件中存在的所有 VLF,并生成这些 VLF 的列表。 此过程可能需要很长时间,具体取决于特定数据库中存在的 VLF 数。 VLF 越多,过程越长。 如果遇到频繁的事务日志自动增长或以小增量手动增长,数据库最终可能会有大量 VLF。 当 VLF 数达到数十万的范围时,可能会遇到以下部分或大部分症状:

  • 在启动期间,一个或多个数据库需要很长时间才能完成恢复SQL Server。
  • 还原数据库需要很长时间才能完成。
  • 尝试附加数据库需要很长时间才能完成。
  • 尝试设置数据库镜像时,遇到错误消息 1413、1443 和 1479,指示超时。
  • 尝试还原数据库时,会遇到与内存相关的错误,例如 701。

检查SQL Server错误日志时,你可能会注意到,在数据库恢复过程的分析阶段之前花费了大量时间。 例如:

2022-05-08 14:42:38.65 spid22s Starting up database 'lot_of_vlfs'.
2022-05-08 14:46:04.76 spid22s Analysis of database 'lot_of_vlfs' (16) is 0% complete (approximately 0 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

此外,还原具有大量 VLF 的数据库时,SQL Server可能会记录 9017 错误:

Database %ls has more than %d virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

有关详细信息,请参阅 MSSQLSERVER_9017

修复具有大量 VLF 的数据库

若要将 VLF 总数保持在合理的数量(例如最多数千个),可以通过执行以下步骤将事务日志文件重置为包含较少数量的 VLF:

  1. 手动收缩事务日志文件。

  2. 使用以下 T-SQL 脚本在一个步骤中手动将文件增加到所需的大小:

    ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>);

    注意

    也可以使用数据库属性页在 SQL Server Management Studio 中执行此步骤。

使用较少的 VLF 设置事务日志文件的新布局后,查看事务日志的自动增长设置并做出必要的更改。 这可确保日志文件避免将来遇到相同的问题。

在执行上述任何操作之前,请确保具有有效的可还原备份,以防以后遇到问题。

若要确定给定实例中所有数据库的当前事务日志大小的最佳 VLF 分布,以及达到所需大小所需的增长增量,可以使用以下 GitHub 脚本 修复 VLF

事务日志的循环性质

事务日志是一种回绕的文件。 例如,假设有一个数据库,它包含一个分成四个 VLF 的物理日志文件。 当创建数据库时,逻辑日志文件从物理日志文件的始端开始。 新日志记录被添加到逻辑日志的末端,然后向物理日志的末端扩张。 日志截断将释放记录全部在最小恢复日志序列号 (MinLSN) 之前出现的所有虚拟日志。 MinLSN 是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。 示例数据库中的事务日志的外观与下图所示相似。

说明如何将物理日志文件划分为虚拟日志的关系图。

当逻辑日志的末端到达物理日志文件的末端时,新的日志记录将回绕到物理日志文件的始端。

说明逻辑事务日志在其物理日志文件中如何环绕的关系图。

这个循环不断重复,只要逻辑日志的末端不到达逻辑日志的始端。 如果经常截断旧的日志记录,始终为到下一个检查点前创建的所有新日志记录保留足够的空间,则日志永远不会填满。 但是,如果逻辑日志的末端真的到达了逻辑日志的始端,将发生以下两种情况之一:

  • FILEGROWTH如果为日志启用了 设置,并且磁盘上有可用空间,则文件将按 growth_increment 参数中指定的量扩展,并将新的日志记录添加到扩展中。 有关设置的详细信息 FILEGROWTH ,请参阅 ALTER DATABASE 文件和文件组选项 (Transact-SQL)

  • FILEGROWTH如果未启用该设置,或者包含日志文件的磁盘的可用空间少于 growth_increment 中指定的空间量,则会生成 9002 错误。 请参考解决事务日志已满的问题,了解详细信息。

如果日志包含多个物理日志文件,则逻辑日志在回绕到首个物理日志文件始端之前,将沿着所有物理日志文件移动。

重要

有关事务日志大小管理的详细信息,请参阅管理事务日志文件的大小

日志截断

日志截断主要用于阻止日志填充。 日志截断从 SQL Server 数据库的逻辑事务日志中删除不活动的虚拟日志文件,释放逻辑日志中的空间以便物理事务日志重用这些空间。 如果事务日志从未被截断,它最终将填满分配给其物理日志文件的所有磁盘空间。 但是,在截断日志前,必须执行检查点操作。 检查点将当前内存中修改的页 (称为 脏页) 和事务日志信息从内存写入磁盘。 执行检查点时,事务日志的不活动部分将标记为可重用。 此后,日志截断可以释放不活动的部分。 有关检查点的详细信息,请参阅数据库检查点 (SQL Server)

下列各图显示了截断前后的事务日志。 第一个图显示了从未截断的事务日志。 当前,逻辑日志使用四个虚拟日志文件。 逻辑日志开始于第一个逻辑日志文件的前面,并结束于虚拟日志 4。 MinLSN 记录位于虚拟日志 3 中。 虚拟日志 1 和虚拟日志 2 仅包含不活动的日志记录。 这些记录可以截断。 虚拟日志 5 仍未使用,并且不是当前逻辑日志的一部分。

显示事务日志在截断前的显示方式的插图。

第二个图显示了日志截断后的情形。 已释放虚拟日志 1 和虚拟日志 2 以供重新使用。 现在,逻辑日志开始于虚拟日志 3 的开头。 虚拟日志 5 仍未使用,它不是当前逻辑日志的一部分。

显示事务日志在截断后如何显示的插图。

除非由于某些原因导致延迟,否则将在以下事件后自动发生日志截断:

  • 简单恢复模式下,在检查点之后发生。
  • 完整恢复模式或大容量日志恢复模式下,在日志备份之后发生(如果自上次备份后出现检查点)。

日志截断可能会因各种因素而延迟。 如果日志截断延迟的时间较长,则事务日志可能会填满磁盘空间。 有关信息,请参阅延迟日志截断的因素排查完整事务日志 (SQL Server错误 9002)

预写事务日志

本节说明预写事务日志在将数据修改记录到磁盘的过程中所起的作用。 SQL Server 使用预写日志 (WAL) 算法,此日志确保在将关联的日志记录写入磁盘后再将数据修改写入磁盘。 这维护了事务的 ACID 属性。

若要了解预写日志的工作原理,请务必了解如何将修改的数据写入磁盘。 SQL Server维护一个缓冲区缓存 (也称为缓冲池) ,当必须检索数据时,它会在其中读取数据页。 在缓冲区缓存中修改页面时,不会立即将其写回到磁盘;相反,页面被标记为 。 一个数据页可以在物理写入磁盘之前进行多个逻辑写入。 对于每次逻辑写入,都会在记录修改的日志缓存中插入一条事务日志记录。 在将关联的脏页从缓冲区缓存中删除并写入磁盘之前,必须将这条些日志记录写入磁盘。 检查点进程定期在缓冲区高速缓存中扫描包含来自指定数据库的页的缓冲区,然后将所有脏页写入磁盘。 CHECKPOINT 可创建一个检查点,在该点保证全部脏页都已写入磁盘,从而在以后的恢复过程中节省时间。

将修改后的数据页从高速缓冲存储器写入磁盘的操作称为刷新页。 SQL Server 具有一个逻辑,它可以在写入关联的日志记录前防止刷新脏页。 日志记录将在刷新日志缓冲区时写入磁盘。 只要事务提交或日志缓冲区已满,就会发生这种情况。

事务日志备份

本节介绍了有关如何备份和还原(应用)事务日志的概念。 在完整恢复模式和批量日志恢复模式下,执行例行事务日志备份(“日志备份” )对于恢复数据十分必要。 可以在任何完整备份运行的时候备份日志。 有关恢复模型的详细信息,请参阅 SQL Server 数据库的备份和还原

在创建第一个日志备份之前,必须先创建完整备份(如数据库备份或一组文件备份中的第一个备份)。 仅使用文件备份还原数据库会较复杂。 因此,建议您尽可能从完整数据库备份开始。 此后,必须定期备份事务日志。 这不仅能最小化工作丢失风险,还有助于事务日志的截断。 通常,事务日志在每次常规日志备份之后截断。

重要

建议经常进行日志备份,其频率应足够支持业务需求,尤其是对损坏的日志存储可能导致的数据丢失的容忍程度。

适当的日志备份频率取决于您对工作丢失风险的容忍程度与所能存储、管理和潜在还原的日志备份数量之间的平衡。 考虑实现恢复策略时所需的恢复时间目标 (RTO) 和恢复点目标 (RPO) ,特别是日志备份节奏。 每 15 到 30 分钟进行一次日志备份可能就已足够。 但是如果您的业务要求将工作丢失的风险最小化,请考虑进行更频繁的日志备份。 频繁的日志备份还有增加日志截断频率的优点,其结果是日志文件较小。

若要限制需要还原的日志备份数,必须定期备份数据。 例如,可以制定这样一个计划:每周进行一次完整数据库备份,每天进行若干次差异数据库备份。

实施恢复策略时,请考虑所需的 RTORPO ,特别是完整和差异数据库备份节奏。

有关事务日志备份的详细信息,请参阅事务日志备份 (SQL Server)

日志链

日志备份的连续序列称为“日志链” 。 日志链从数据库的完整备份开始。 通常,只有在首次备份数据库时,或者在恢复模式从简单恢复切换到完整恢复或大容量日志恢复之后,才会启动新的日志链。 除非在创建完整数据库备份时选择覆盖现有备份集,否则现有的日志链将保持不变。 在该日志链保持不变的情况下,便可从介质集中的任何完整数据库备份还原数据库,然后再还原相应恢复点之前的所有后续日志备份。 恢复点可以是上次日志备份的结尾,也可以是任何日志备份中的特定恢复点。 有关详细信息,请参阅事务日志备份 (SQL Server)

若要将数据库还原到故障点,必须保证日志链是完整的。 也就是说,事务日志备份的连续序列必须能够延续到故障点。 此日志序列必须开始的位置取决于要还原的数据备份类型:数据库、部分或文件。 对于数据库备份或部分备份,日志备份序列必须从数据库备份或部分备份的结尾处开始延续。 对于一组文件备份,日志备份序列必须从整组文件备份的开头开始延续。 有关详细信息,请参阅应用事务日志备份 (SQL Server)

还原日志备份

还原日志备份会前滚事务日志中记录的更改,以在日志备份操作开始时重新创建数据库的确切状态。 还原数据库时,必须还原在还原的完整数据库备份之后或从还原的第一个文件备份开始创建的日志备份。 通常情况下,在还原最新数据或差异备份后,必须还原一系列日志备份直到到达恢复点。 然后恢复数据库。 这将回滚所有在恢复开始时未完成的事务并使数据库联机。 恢复数据库后,无法再还原任何备份。 有关详细信息,请参阅应用事务日志备份 (SQL Server)

检查点和日志的活动部分

检查点将脏数据页从当前数据库的缓冲区高速缓存刷新到磁盘上。 这最大限度地减少了数据库完整恢复时必须处理的活动日志部分。 在完整恢复时,需执行下列操作:

  • 前滚系统停止之前尚未刷新到磁盘上的日志记录修改信息。
  • 与不完整事务(例如没有 COMMIT 或 ROLLBACK 日志记录的事务)关联的所有修改都会回滚。

检查点操作

检查点在数据库中执行下列过程:

  • 将记录写入标记检查点起点的日志文件。

  • 将为检查点记录的信息存储在检查点日志记录链内。

    检查点中记录的一条信息是第一条日志记录的日志序列号 (LSN),该 LSN 必须存在才能进行成功的数据库范围的回滚。 该 LSN 称为“最小恢复 LSN”(“MinLSN”)。 MinLSN 是下列各项中的最小者:

    • 检查点起点的 LSN。
    • 最早的活动事务起点的 LSN。
    • 尚未传递到分发数据库的最早复制事务开始的 LSN。

    检查点记录还包含所有已修改数据库的活动事务的列表。

  • 如果数据库使用简单恢复模式,则标记在 MinLSN 前重用的空间。

  • 将所有脏日志和数据页写入磁盘。

  • 将标记检查点结束的记录写入日志文件。

  • 将这条链起点的 LSN 写入数据库引导页。

导致检查点的活动

下列情况下将出现检查点:

  • 显式执行 CHECKPOINT 语句。 用于连接的当前数据库中出现检查点。
  • 在数据库中执行了最小日志记录操作,例如,在使用大容量日志恢复模式的数据库中执行大容量复制操作。
  • 已经使用 ALTER DATABASE 添加或删除了数据库文件。
  • 通过 SHUTDOWN 语句或通过停止 SQL Server (MSSQLSERVER) 服务停止了 SQL Server 实例。 任一操作都会在 SQL Server 实例的每个数据库中生成一个检查点。
  • SQL Server 实例在每个数据库内定期生成自动检查点,以减少实例恢复数据库所需的时间。
  • 进行了数据库备份。
  • 执行了需要关闭数据库的活动。 例如,AUTO_CLOSE 设置为 ON 并且关闭了数据库的最后一个用户连接,或者执行了需要重新启动数据库的数据库选项更改。

自动检查点

SQL Server 数据库引擎生成自动检查点。 自动检查点之间的间隔基于使用的日志空间量以及自上一个检查点以来经历的时间。 如果只在数据库中进行了很少的修改,自动检查点之间的时间间隔可能变化很大并且很长。 如果修改了大量数据,自动检查点也会经常出现。

使用“恢复间隔” 服务器配置选项为服务器实例上的所有数据库计算自动检查点之间的间隔。 此选项指定数据库引擎在系统重新启动时恢复数据库所用的最长时间。 数据库引擎将估计在执行恢复操作期间自己在“恢复间隔” 内能够处理多少条日志记录。

自动检查点之间的间隔也取决于恢复模式:

  • 如果数据库使用的是完整恢复模式或批量日志恢复模式,则每当日志记录数达到数据库引擎估计在“恢复间隔”选项中指定的时间内可以处理的数量时,便会生成一个自动检查点。

  • 如果数据库使用的是简单恢复模式,只要日志记录数达到下面两个值中较小的那个值,就会生成自动检查点:

    • 日志已满 70%。
    • 日志记录数达到数据库引擎估计在“恢复间隔”选项指定的时间内能够处理的记录数。

有关设置恢复间隔的信息,请参阅 配置恢复间隔服务器配置选项

提示

-k SQL Server 高级设置选项使数据库管理员能够根据某些类型检查点的 I/O 子系统的吞吐量来限制检查点 I/O 行为。 安装 -k 选项适用于自动检查点和任何其他不受限制的检查点。

如果数据库使用的是简单恢复模式,自动检查点将截断事务日志中没有使用的部分。 但是,如果数据库使用的是完整恢复模式或大容量日志恢复模式,则自动检查点不会截断该日志。 有关详细信息,请参阅 事务日志

现在,CHECKPOINT 语句提供了一个可选的 checkpoint_duration 参数,它指定完成检查点所需的秒数。 有关详细信息,请参阅 CHECKPOINT

活动日志

日志文件中从 MinLSN 到最后写入的日志记录这一部分称为日志的活动部分,或者称为活动日志。 这是进行数据库完整恢复所需的日志部分。 永远不能截断活动日志的任何部分。 所有的日志记录都必须从 MinLSN 之前的日志部分截断。

下图显示了具有两个活动事务的结束事务日志的简化版本。 检查点记录已压缩成单个记录。

图示了包含两个活动事务和一条压缩检查点记录的事务日志结束。

LSN 148 是事务日志中的最后一条记录。 在处理 LSN 147 处记录的检查点时,Tran 1 已经提交,而 Tran 2 是唯一的活动事务。 这就使 Tran 2 的第一条日志记录成为执行最后一个检查点时处于活动状态的事务的最旧日志记录。 这使 LSN 142(Tran 2 的开始事务记录)成为 MinLSN。

长时间运行的事务

活动日志必须包括所有未提交事务的每一部分。 启动事务且未提交或回滚的应用程序会阻止数据库引擎推进 MinLSN。 这可能会导致两种问题:

  • 如果系统在事务执行了许多未提交的修改后关闭,以后重新启动时,恢复阶段所用的时间将比“恢复间隔” 选项指定的时间长得多。
  • 日志可能会变得非常大,因为日志不能被截断到 MinLSN 之后。 即使数据库使用的是简单恢复模式,这种情况也有可能出现,在简单恢复模式下,每次执行自动检查点操作时通常都会截断事务日志。

从 SQL Server 2019 (15.x) 和 Azure SQL 数据库 开始,可以使用加速数据库恢复来避免恢复长时间运行的事务和上述问题。

复制事务

日志读取器代理监视已为事务复制配置的每个数据库的事务日志,并将已设复制标记的事务从事务日志复制到分发数据库中。 活动日志必须包含标记为要复制但尚未传递到分发数据库的所有事务。 如果未及时复制这些事务,它们可能会阻止截断日志。 有关详细信息,请参阅 事务复制

后续步骤

有关事务日志和日志管理最佳做法的其他信息,建议阅读以下文章和书籍。