管理事务日志文件的大小

  • 项目

适用于：SQL Server

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

本文适用于 SQL Server。 虽然非常相似，但有关管理 Azure SQL 托管实例中事务日志文件大小的信息，请参阅管理 Azure SQL 托管实例中数据库的文件空间。 有关 Azure SQL 数据库的信息，请参阅在 Azure SQL 数据库中管理数据库的文件空间。

了解数据库存储空间的类型

了解以下存储空间数量对于管理数据库的文件空间非常重要。

数据库数量 定义 注释
已用数据空间 用于存储数据库数据的空间量。 通常，已用空间会在执行插入操作时增大，在执行删除操作时减小。 在某些情况下，已用空间不会在执行插入或删除操作时发生变化，具体取决于该操作涉及的数据数量和模式，以及是否有任何碎片。 例如，从每个数据页中删除一行不一定会减小已用空间。
已分配的数据空间 可用于存储数据库数据的格式化文件空间量。 已分配的空间量会自动增长，但执行删除操作后永远不会减小。 此行为可确保将来的插入操作速度更快，因为不需要重新设置空间的格式。
已分配但未使用的数据空间 已分配的数据空间量与已使用的数据空间量之间的差值。 此数量表示通过收缩数据库数据文件可回收的最大可用空间量。
数据最大大小 可用于存储数据库数据的最大空间量。 已分配的数据空间量在增长后不能超过数据最大大小。

下图演示了数据库的不同存储空间类型之间的关系。

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

查询单一数据库的文件空间信息

使用以下查询，返回已分配的，以及已分配但未使用的数据库文件空间量。 查询结果以 MB 为单位。

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

监视日志空间使用情况

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

若要了解有关日志文件的当前大小、最大大小以及文件的自动增长选项的信息，还可以在sys.database_files中针对此日志文件使用size、max_size、growth列。

重要

避免日志磁盘重载。 请确保日志存储可以承受 IOPS 和事务加载的低延迟需求。

收缩日志文件

若要通过将文件中的可用空间返回到操作系统来减小物理日志文件的物理大小，请收缩日志文件。 只有当事务日志文件包含未使用的空间时，收缩才会产生影响。

如果日志文件已满（可能是由于打开的事务），请调查 什么正在阻止事务日志截断。

注意

收缩操作不应被视为常规维护操作。 由于常规定期业务操作而增长的数据和日志文件不需要收缩操作。 Shrink 命令在运行时可能会影响数据库的性能，请尽量在使用率较低的时候运行它。 如果常规应用程序工作负载会导致文件再次增长到相同的分配大小，则不建议收缩数据文件。

注意收缩数据库文件的潜在负面影响，请参阅收缩后索引维护。

收缩事务日志前，请记住可能延迟日志截断的因素。 如果在日志收缩后还需要存储空间，则会再次增加事务日志，导致在增加日志操作期间产生性能开销。 有关详细信息，请参阅建议。

仅当数据库处于联机状态，而且至少一个虚拟日志文件 (VLF) 可用时，才能收缩日志文件。 在某些情况下，直到下一个日志截断后，才能收缩日志。

能够延长VLF活动时间的因素（如长时间运行的事务）可以限制甚至阻止日志收缩。 有关详细信息，请参阅可能延迟日志截断的因素。

收缩日志文件可删除一个或多个不包含逻辑日志任何部分的 VLF（即不活动的 VLF）。 收缩事务日志文件时，将从日志文件末端删除不活动的 VLF，以将日志减小到接近目标大小。

有关收缩操作的详细信息，请查看以下链接：

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

监视日志文件收缩事件

监视日志空间

收缩后的索引维护

对数据文件执行完收缩操作后，索引可能会变得碎片化。 这会使某些工作负载的性能优化不再有效，例如使用大型扫描进行的查询。 如果在收缩操作完成后性能下降，请考虑通过索引维护来重新生成索引。 请记住，重新生成索引需要使用数据库中的可用空间，因此可能会导致已分配的空间增加，从而抵消收缩的影响。

有关索引维护的详细信息，请参阅优化索引维护以提高查询性能并减少资源消耗。

添加或扩大日志文件

可以通过扩大现有日志文件（如果磁盘空间允许）或将日志文件添加至数据库（通常是其他磁盘上的数据库）来获得空间。 一个事务日志文件就足够了，除非日志空间不足且保留日志文件的卷上的磁盘空间也不足。

  • 要将日志文件添加到数据库，请使用 ALTER DATABASE 语句的 ADD LOG FILE 子句。 添加日志文件可以使日志获得空间。
  • 要扩大日志文件，请使用 ALTER DATABASE 语句的 MODIFY FILE 子句，指定 SIZE 和 MAXSIZE 语法。 有关详细信息，请参阅 ALTER DATABASE (Transact-SQL) 文件和文件组选项。

有关详细信息，请参阅建议。

优化 tempdb 事务日志的大小

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

你可以通过在启动或重新启动服务器实例之后增加 tempdb 事务日志的大小来避免此开销。 有关详细信息，请参阅 tempdb Database。

控制事务日志文件的增长

使用 ALTER DATABASE (Transact-SQL) 文件和文件组选项语句管理事务日志文件的增长。 请注意以下事项：

  • 要更改当前文件大小（以 KB、MB、GB 和 TB 为单位），请使用 SIZE 选项。
  • 要更改增量，请使用 FILEGROWTH 选项。 如果值为 0，则表明自动增长已设置为关闭，且不允许增加空间。
  • 要控制日志文件的最大大小（以 KB、MB、GB 和 TB 为单位）或将增长设置为 UNLIMITED，请使用MAXSIZE选项。

有关详细信息，请参阅建议。

建议

下面是使用事务日志文件时的一些一般建议：

  • FILEGROWTH 选项设置的事务日志的自动增长 (autogrow) 增量必须足够大，以领先于工作负载事务的需求。 因此，为了避免经常向日志文件中扩充内容，应该采用足够大的文件增量。 要正确设置事务日志的大小，建议监视以下时间内所占用的日志数量：

    • 执行完整备份所需的时间，因为日志备份在其完成后才能进行。
    • 最大型索引维护操作所需的时间。
    • 在数据库中执行最大批操作所需的时间。

  • 使用 FILEGROWTH 选项设置数据和日志文件的 autogrow 时，建议首选使用 size 而不是使用 percentage 进行设置，以便更好地控制增长比，因为 percentage 表示的是日益增长量。

    • 在 SQL Server 2022 (16.x) 之前的版本中，事务日志不能使用即时文件初始化，因此延长的日志增长时间尤其重要。

    • 在 SQL Server 2022 (16.x)（所有版本）及之后的版本以及 Azure SQL Database 中，即时文件初始化可对不超过 64 MB 的事务日志增长事件提供帮助。 新数据库的默认自动增长大小增量为 64 MB。 大于 64 MB 的事务日志文件自动增长事件则无法利用即时文件初始化。

    • 最佳做法是，针对日志事务，请勿将 FILEGROWTH 选项值设置为超过 1,024 MB。 FILEGROWTH 选项的默认值为：

      版本 默认值
      自 SQL Server 2016 (13.x) 起 数据 64 MB。 日志文件 64 MB。
      自 SQL Server 2005 (9.x) 起 数据 1 MB。 日志文件 10%。
      SQL Server 2005 (9.x) 之前 数据 10%。 日志文件 10%。

  • 小型的自动增长增量可能生成过多的VLF并且可能降低性能。 若要确定给定实例中所有数据库的当前事务日志大小的最佳 VLF 分发，以及实现所需大小需要的增长量，请参阅此脚本以分析和修复 VDF（由 SQL Tiger Team 提供）。

  • 大型自动增长增量可能会导致两个问题：

    • 大型自动增长增量可能会导致数据库在分配新空间时暂停，这可能会导致查询超时。
    • 大型的自动增长增量可能生成过少的大型VLF并且也可能影响性能。 若要确定给定实例中所有数据库的当前事务日志大小的最佳 VLF 分发，以及实现所需大小需要的增长量，请参阅此脚本以分析和修复 VDF（由 SQL Tiger Team 提供）。

  • 即使启用自动增长，如果增长速度不能满足查询需求，也可能收到提示事务日志已满的消息。 有关更改增长增量的详细信息，请参阅ALTER DATABASE (Transact-SQL) 文件和文件组选项。

  • 在数据库中有多个日志文件不会以任何方式提升性能，因为事务日志文件不会像同一文件组中的数据文件一样使用比例填充。

  • 日志文件可以设为自动收缩。 但是，不建议这样做，auto_shrink 数据库属性默认设为 FALSE。 如果 auto_shrink 设置为 TRUE，则仅当其空间的 25% 以上未使用时，自动收缩才会减少文件的大小。

后续步骤