☰

SQL Server 日志空间使用量为何会超过 100%？

tag-icon • tag-icon ms-sql
SQL Server 日志空间使用量为何会超过 100%？

我们看到报告事务日志的应用程序错误（异常）“由于 ACTIVE_TRANSACTION 而已满“。 没有报告任何待处理交易DBCC OPENTRAN。

运行时DBCC SQLPERF('logspace')我发现日志大小只有 1.3MB，但日志空间使用量显示为 107.7%

该数据库配置了一个最大大小超过 2 TB 的日志文件，初始大小为 2MB，自动增长设置为 10%。恢复模型设置为简单。

日志空间使用量怎么会超过100%，还有这么多可用空间，为什么会产生异常呢？

答案1

寻找罪魁祸首

你可以跑sp_WhoIsActive并查看谁在运行什么，并查看正在运行的进程/活动事务的逻辑。检查是否可以优化任何 T-SQL 查询以使其运行得更快或作为较小的事务运行，以便更快地释放日志文件中未使用的可用空间以供重复使用。

这是设计使然

SQL Server简单恢复模型

每个事务仍写入事务日志，但一旦事务完成并且数据已写入数据文件，事务日志文件中使用的空间现在可由新事务重新使用。

来源

我见过一些案例，在SIMPLE恢复模型设置中，长时间运行的事务使事务日志变得非常大。该事务实际上失败了，并且需要同样长的时间才能回滚。因此，长时间运行的事务、性能不佳或未优化的查询编写不当都可能导致此问题。

一旦将空间分配给SIMPLE恢复模型数据库事务日志文件，则未使用的事务日志可用空间或者每次自动增长来自操作系统级别的可用空间，事务日志文件将保留新的空间，直到发生文件收缩操作，例如DBCC SHRINKFILE (database_log, 2048)。

重要的：例如，当发生文件收缩操作时DBCC SHRINKFILE (database_log, 2048)，它只会将事务日志中未使用的日志空间作为可用空间释放回操作系统。在文件收缩操作期间，不会释放事务日志中写入的正在运行的事务。

缩小日志文件

缩小日志文件的问题在于，下次运行大型事务或编写不当的查询时，日志文件将再次填满，您需要重复缩小操作。找到并解决根本问题，以便永久修复此问题。与此同时，继续缩小日志文件。

解决根本原因

根本问题可能是一个查询，因此确定谁在做什么并联系他们并报告您的发现的问题会给他们施加压力，要求他们修复他们的逻辑以免破坏服务器磁盘空间分区；考虑优化查询逻辑以提高性能。

  • 追查根本原因

    有时根本原因不是长时间运行的事务——例如，可能是有人设置了复制，但从未正确地将其关闭。首先检查log_reuse_wait_desc in sys.databases：

    SELECT name, log_reuse_wait_desc FROM sys.databases;

    但请记住，这只是日志无法立即缩小的原因的一个快照。

    然后，如果你在那里找不到任何有趣的东西，你可以将 sp_WhoIsActive 记录到表中 捕捉人们何时执行BEGIN TRAN，并使其会话保持打开状态数小时。查找长时间运行的事务，与所有者交谈，看看他们是否可以将工作分成几个小块，而不是一个大事务。

  • 日志文件空间元数据

    DBCC SQLPERF(logspace)如果您只对数据库日志文件的消耗感兴趣，则绝对有用的命令。它提供 SQL Server 实例上每个数据库的每个日志文件的累计大小以及占用的空间量（占总日志文件大小的百分比）。 缺点是结果是数据库的聚合。 如果您有多个日志文件，则结果将显示在数据库级别，而不是文件级别。

    虽然此 DBCC 命令在您查看由于日志备份计划不充分或日志文件大小不正确而出现的问题时很方便，但它并不能为您提供做出有关日志文件大小、调整备份计划频率或恢复模型的明智决策所需的所有信息。

    来源

相关内容