TempDB 日誌文件增長 - 問題

問題：

在其中一個生產機器上，TempDB 事務日誌文件意外增長，填滿了驅動器

原來是 32 GB，但現在是 240 GB

問題：

1. TempDB 日誌文件的使用機制是什麼？

TempDB 處於 SIMPLE 恢復模式，什麼可以延遲 TempDB 日誌文件截斷

（並導致其增長）？

2. 當 TempDB 數據文件或日誌文件正在增長時，我如何知道或收到警報？

是否有為此的擴展事件或其他方式知道，並且可以在增長發生時通過電子郵件發送警報？

3. 性能監控指標

日誌文件可能是 240 GB，但目前它確實是空的

是否有一個指標可用於在任何給定時間點監控 TempDB 數據/日誌文件內的已用空間量？

我可以看到有Active Temp Tables, Temp Tables Creation Rate,Worktables Created/sec等，但這些並不能準確顯示我想看到的內容

4. 驅動空間監控

當 TempDB 驅動器上的可用空間低於 20%、10% 時，SQL Server 或 Windows 中是否有本機工具可以提醒我？

5. 如何找出歷史上哪個會話或任務填充了 TempDB 數據或日誌文件？

我可以通過查詢以下內容來查看目前在 TempDB 上使用哪些使用者/系統會話：sys.dm_db_session_space_usage。但是我怎麼知道哪個使用者/任務消耗了 TempDB 的大部分，比如說，2 小時前，而不必坐下來查詢那個視圖？

6. sys.dm_db_session_space_usage

user_objects_alloc_page_count- （減號）user_objects_dealloc_page_count= 區別在於 TempDB 中目前正在使用的會話（以頁面為單位）？或者它不那樣工作？

如果您至少知道其中幾個問題，我們非常感謝您的幫助

為什麼 tempdb 日誌可能會增長

延遲 tempdb 日誌文件截斷的常見嫌疑人是長時間執行的事務。您可以在問題發生時執行這樣的查詢，以查看無法截斷日誌的原因：

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

原因在此 MS 文件頁面上進行了描述。

監控和警報

問題 2 - 5 都可以通過購買專用監控工具（如 Sentry One 或 SolarWinds 等）來解決。

您當然可以使用“本土”方法完成所有這些事情，但它們不可避免地會出現監控工具供應商多年來思考和解決的差距和失敗。

例如，您可以使用擴展事件 (XE) 來監視文件增長事件 - Aaron Bertrand 有一篇關於在sqlserver.database_file_size_change此處對事件執行此操作的文章：刪除預設跟踪 - 第 2 部分。

要對文件增長發出警報，您可以有一個 SQL 代理作業來定期查詢此事件數據（直接或通過Aaron 的視圖），例如每 1 / 5 / 15 分鐘一次，然後通過電子郵件發送給您（如果有的話）框架。

獲取數據和日誌文件中使用的空間*，*以及使用/可用的磁碟空間，在本網站的另一個問答中討論，所以我不會在這裡重複：查詢以報告磁碟空間分配和已用空間

同樣，提醒這一點的低技術方式可能是通過代理工作。

SQL Server 不會記錄在給定時間使用 tempdb 的人員或對象 - 您必須將 DMV 查詢結果寫入表，定期檢查它們等。

目前 tempdb 使用情況

這包含在 的結果中sp_WhoIsActive。您可以考慮直接使用它，或者至少基於該proc 的相關部分進行計算。

您需要將user_objects_alloc_page_count和internal_objects_alloc_page_count（包括 LOB 變數和溢出）相加，然後減去dealloc兩者的版本以獲得事物的“目前”狀態。

您會注意到，其中sp_WhoIsActive還包括來自dm_db_task_space_usage此的數據，因為一些分配將顯示在那里而不是在其他視圖中。您可以在我的部落格上看到一個詳細範例，其中從 .NET 應用程序流式傳輸的 LOB 參數將顯示在此視圖中：Invisible tempdb Killer

引用自：https://dba.stackexchange.com/questions/271860