主页 / dba / 问题 / 289586
Accepted
BeginnerDBA
Asked: 2021-04-10 21:50:55 +0800 CST

TempDB 日志文件增长之谜的紧急帮助

  • 772

对于过去上周的 tempdb 问题，我需要一些紧急指导：

tempDB 日志文件从特定时间开始增长，比如说下午 2 点。并且几乎继续增长到 150 GB，然后在 10 个小时左右后下降。

我使用了此处列出的各种查询，但没有显示任何内容，因为在此期间没有长时间运行的事务。

混合工作负载的查询不断涌现。没有这样的卡住交易。

此实例上的用户数据库都是日志传送设置的一部分，日志备份每 15 分钟发生一次。

另外我添加了 Xevent

CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER ADD EVENT 
sqlserver.database_file_size_change(SET collect_database_name=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE ([database_id]=(2))) ADD TARGETpackage0.event_file(SET filename=N'C:\ExtendedEvents\TempDBGrowth.xel',max_file_size=(100),max_rollover_files=(25)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

即使在 XE 中也没有任何内容被捕获，并且在那 10 个小时内它只是空白。

我不知道是什么在吃我的 tempDB 日志，因为没有任何查询或 XE 获取我的数据。我从来没有见过这个东西，通常使用 XE 来获得数据库增长，但在这里我一无所知。

我正在跑步DBCC SQLPERF(logspace)，我可以看到使用的日志每 5 分钟左右不断增加 1 Gb。我们将 TempDB 日志设置为最大 200 GB，但到了 10 小时，它几乎达到 160-180 GB，然后突然下降。

几乎每次在查询下方运行都会给出 ACTIVE TRANSACTION。

SELECT log_reuse_wait, log_reuse_wait_desc 
FROM sys.databases d 
WHERE database_id = 2;

请帮助我可能做错了什么以及为什么我看不到任何使用 TempDB 日志文件使用的事务？

更新 - 按照@JD 的回答要求，我检查并确认没有数据库邮件或服务代理在内部被用于导致增长：

然而在上周我看到 tempdb 的日志文件的增长是曲折的，曲折增长的趋势有点高：

我需要帮助理解：-

事务日志的增长是如何增长到 80%，然后再下降到 0，然后再增长和增长等等，达到 lmoast max 90-95%。

我确认没有手动收缩。可能有多个进程参与，因为这是全天使用 tempdb 的 OLTP 服务器。只是不明白上周它是如何增长到接近其最大容量的？

谢谢

sql-server performance

3 个回答

  1. Best Answer

    您所看到的是 tempdb 事务日志文件的设计。我希望微软对此有更好的文档。

    事务日志增长是如何增长到 80%，然后再降到 0，然后再增长和增长等等，几乎达到最大 90-95%。

    从第一篇参考文章：

    Tempdb 在发生崩溃时不会恢复，因此无需将脏 tempdb 页面强制写入磁盘，除非lazywriter 进程（缓冲池的一部分）必须为其他数据库的页面腾出空间。

    仅当 tempdb 日志文件 已满 70%时才会为 tempdb 执行检查点——这是为了尽可能防止 tempdb 日志增长（请注意，长时间运行的事务本质上仍然可以将日志作为人质并阻止其清除，就像在用户数据库中一样）。

    当然，当您发出手动检查点时，所有脏页都会被刷新，但对于自动检查点，它们不会。

    我建议您监控您的 tempdb 事务日志使用情况并将其设置为合理的大小，在正常工作负载下它不必增长和缩小。

    我不知道为什么你的 tempdb 日志文件填充到 100%。日志文件没有被截断一定是有原因的。

    间接检查点会有帮助吗？

    在大多数情况下没有。

    任何符合 tempdb 中“急切写入”条件的未记录“批量”操作都不会被恢复写入器（运行间接检查点的内部线程）刷新。

    这就提出了一个重要的问题：在 tempdb 上最少记录哪些数据加载操作？了解这一点很重要，因为间接检查点不会刷新 tempdb 上的最小记录操作。以下列表可用于帮助您了解 tempdb 上的哪些加载操作将被最少记录，哪些不会。

    有关详细信息，请阅读Tempdb – 这是Fabiano Amorim 所不知道的问题

    我注意到这个问题是针对SQL Server 2014，如果有人正在阅读此SQL Server 2016文档以阅读 Microsoft 的以下文档。

    1. 数据库检查点 (SQL Server) - 检查“间接检查点”部分。
    2. 更改数据库 (SQL Server) 的目标恢复时间。
    3. 间接检查点和 tempdb—— Parikshit Savjani的好、坏和不产生影响的调度程序

    参考：

    1. 检查点对 tempdb 有什么作用？保罗·兰德尔
    2. 请参阅问答部分存储引擎内部：缓冲池中有什么？保罗·兰德尔
    • 2

  2. 尝试使用 TempDBInfo 过程，它会显示TempDB 文件中当前的内容以及使用最多的用户

    https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/TempDB%20-%20TempDBInfo.sql

    程序还没有完全完成，我将重新设计它显示摘要信息的方式，并且我计划添加日志记录功能

    但它仍然擅长查看谁是当前消费者（哪个会话或任务使用最多），并可能帮助您诊断问题

    这里它向我展示了什么：

    临时数据库信息

    如果您对SP有任何意见或建议，请告诉我

    • 1

  3. 有趣的是，您看不到任何正在运行的事务，但它继续增长，因为必须有一个潜在的原因。您是否探索过亚伦在您链接的答案中提到的最后一条建议？

    您可能还认为您的 tempdb 日志使用可能是由您很少或无法控制的内部进程引起的 - 例如数据库邮件、事件通知、查询通知和服务代理都以某种方式使用 tempdb。您可以停止使用这些功能，但如果您正在使用它们，则无法指定它们使用 tempdb 的方式和时间。

    除此之外，您可能想开始研究WhoIsActive，这是 Adam Machanic 的一个惊人查询，它基本上显示了服务器上每个正在运行的查询以及一些性能分析信息，和/或Brent的First Responder Kit中的一些程序Ozar 的团队，例如sp_BlitzFirst、sp_BlitzWho或sp_BlitzCache。您甚至可以尝试安排一个定期运行并缓存其中任何一个的作业，这样您就可以了解 SQL Server 发生的事情之前、期间和之后的故事。

    • 0

相关问题