管理事务日志文件的大小

适用于：SQL Server

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

本文适用于 SQL Server。 虽然类似，但有关管理Azure SQL 托管实例中事务日志文件大小的信息，请参阅管理Azure SQL 托管实例中数据库的文件空间。 有关 Azure SQL 数据库的信息，请参阅在 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) 可用时，才能收缩日志文件。 在某些情况下，只能在下一次日志截断后收缩日志。

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

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

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

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

• DBCC SHRINKFILE (Transact-SQL)

• 收缩文件

监视日志文件收缩事件

• Log File Auto Shrink Event Class。

监视日志空间

• sys.dm_db_log_space_usage (Transact-SQL)

• sys.database_files (Transact-SQL)（请参阅日志文件或文件的size、max_size和growth列。）

收缩后的索引维护

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

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

添加或扩大日志文件

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

要将日志文件添加到数据库，请使用 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) 文件和文件组选项语句管理事务日志文件的增长。 注意以下事项：

使用“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%。

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

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

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

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

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

日志文件可以设为自动收缩。 但是，不建议这样做， 默认情况下，auto_shrink 数据库属性设置为 FALSE。 如果 auto_shrink 设置为 TRUE，则仅当其空间的 25% 以上未使用时，自动收缩才会减少文件的大小。 - 文件收缩为仅 25% 的文件未使用空间或文件的原始大小（以较大者为准）。 - 有关更改auto_shrink属性设置的信息，请参阅“查看或更改数据库的属性”和“ALTER DATABASE SET 选项”（Transact-SQL）。

相关内容

• BACKUP (Transact-SQL)
• 解决事务日志已满的问题（SQL Server 错误 9002）
• SQL Server 事务日志体系结构和管理指南中的事务日志备份
• 事务日志备份 (SQL Server)
• ALTER DATABASE (Transact-SQL) 文件和文件组选项