主页 / dba / 问题 / 78625
Accepted
Gabe
Asked: 2014-10-08 11:55:04 +0800 CST

查看在简单恢复模型中使用了多少事务日志

  • 772

我们在简单恢复模型中有一个 DW 数据库。每天晚上，我们从不同来源加载数据并运行一些过程。我想看看在这个需要数小时才能运行的夜间负载中实际使用了多少事务日志。

目前，由于我们最近运行的一次性清理查询，日志已经膨胀到超过 50GB。由于简单恢复模式，日志上的已用空间接近 0%。我们想将日志文件缩小到更合理的大小。我怀疑每晚负载只使用 5GB，但有没有办法真正找到它？我想每隔一两晚轮询一次数据库，并在日志表中捕获已使用和未使用的空间。有没有更好的办法？

sql-server-2012 transaction-log

1 个回答

  1. Best Answer

    似乎是一种合适的方法。

    创建日志表：

    CREATE TABLE dbo.LogSpace
(
  dt DATETIME NOT NULL DEFAULT SYSDATETIME(),
  dbname SYSNAME, 
  log_size_mb DECIMAL(22,7),
  space_used_percent DECIMAL(8,5),
  [status] BIT
);

    在加载之前和之后执行此操作：

    INSERT dbo.LogSpace(dbname, log_size_mb, space_used_percent, [status])
EXEC sp_executesql N'DBCC SQLPERF(LogSpace) WITH NO_INFOMSGS;';

    或者，删除与此特定数据库无关的任何行：

    DELETE dbo.LogSpace WHERE dbname <> N'yourdb';

    然后您可以比较用于任何给定日期或您收集的所有日期的前后大小/空间。

    ;WITH x AS
(
  SELECT dbname, dt,
  duration = DATEDIFF(SECOND, LAG(dt) OVER 
    (PARTITION BY dbname ORDER BY dt), dt),
  [current] = space_used_percent, 
  previous = LAG(space_used_percent) OVER
    (PARTITION BY dbname ORDER BY dt),
  rn = ROW_NUMBER() OVER 
    (PARTITION BY dbname ORDER BY dt),
  log_size_mb
 FROM dbo.LogSpace
)
SELECT * FROM x WHERE rn % 2 = 0;

    请记住，在您的过程中发生的检查点实际上可以使日志空间得到重用；我记得最近做了一些性能测试，并且在某些操作之后space_used_percent实际上发生了故障。因此，您可能希望获取几天内观察到的最大值（并且可能更频繁地运行它——在这种情况下，您需要一个稍微不同的查询，它不假设成对的连续行与任何特定活动相关），而不是只是依赖于加载后它是如何结束的。

    还要确保日志文件的自动增长设置是合理的——您不需要 1MB 或 10%，但您也不需要 10GB。由于日志文件的自动增长事件将 (a) 使所有事务等待并且 (b) 不会从即时文件初始化中受益，因此您希望在日志文件在异常操作（如数据）期间必须增长多少次之间取得良好的平衡清理，以及任何个体生长事件发生需要多长时间。如果该事件足够新，您可以在默认跟踪中查看这些事件以查看当时花费了多长时间。

    • 3

相关问题