通过

管理事务日志文件的大小

适用于：SQL Server

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

本文适用于 SQL Server。 尽管此过程类似，但有关 Azure SQL 中的文件空间管理，请参阅：

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

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

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

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

该图说明了数据库不同类型的存储空间之间的关系。

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

使用以下查询，返回已分配的，以及已分配但未使用的数据库文件空间量。 查询结果以 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 和事务加载的低延迟需求。

收缩日志文件

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

如果日志文件已满，可能是由于打开的事务，请调查 阻止事务日志截断的内容。

注意

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

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

在收缩事务日志之前，请记住 可能会延迟日志截断的因素。 如果在日志收缩后再次需要存储空间，事务日志将再次增长，从而在日志增长操作期间引入性能开销。 有关详细信息，请参阅 建议。

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

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

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

有关收缩作的详细信息，请查看以下资源：

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

监视日志文件收缩事件

监视日志空间

收缩作后的索引维护

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

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

添加或扩大日志文件

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

  • 要将日志文件添加到数据库，请使用 ADD LOG FILE 语句的 ALTER DATABASE 子句。 此作允许日志增长。
  • 要扩大日志文件，请使用 MODIFY FILE 语句的 ALTER DATABASE 子句，指定 SIZE 和 MAXSIZE 语法。 有关详细信息，请参阅 ALTER DATABASE （Transact-SQL） 文件和文件组选项。

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

优化 tempdb 事务日志的大小

重启服务器实例会将数据库的事务日志 tempdb 大小调整为其原始预增长大小。 这种调整大小可以减少事务日志的性能 tempdb 。

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

控制事务日志文件的增长

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

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

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

建议

以下是在使用事务日志文件时要考虑的一些常规建议：

  • 按选项设置 FILEGROWTH 的事务日志的自动增长（自动增长）增量必须足够大，才能提前满足工作负荷事务的需求。 因此，为了避免经常向日志文件中扩充内容，应该采用足够大的文件增量。 正确调整事务日志大小的良好提示是监视在以下过程中占用的日志量：

    • 运行完整备份所需的时间，因为日志备份在完成之前无法进行。
    • 最大型索引维护操作所需的时间。
    • 在数据库中运行最大批处理所需的时间。

  • 使用 FILEGROWTH 此选项为数据和日志文件设置自动增长时，最好将其 设置为大小 而不是 百分比 ，以便更好地控制增长比率，因为百分比是不断增长的数量。

    • 在 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%。

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

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

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

  • 即使启用了自动增长，也可以收到一条消息，指出如果事务日志增长速度不够快，以满足查询需求，则会收到一条消息。 有关更改增长增量的详细信息，请参阅 ALTER DATABASE （Transact-SQL） 文件和文件组选项。

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

日志文件可以设为自动收缩。 但是，我们不建议使用此配置， AUTO_SHRINK 并且数据库属性默认设置为 FALSE。 如果 AUTO_SHRINK 设置为 TRUE，则仅当超过 25% 的空间未使用时，自动收缩才会减小文件的大小。

相关内容