tempdb日誌檔案暴增分析

z_cloud_for_SQL發表於2023-02-28
背景

某醫院 資訊科接到CIS系統磁碟空間不足告警，透過排查發現tempdb的日誌檔案暴增，已經漲到了130G左右，並且還在持續增長中。 需要我們緊急排查原因。

現象 1


登陸到伺服器裡，確實看到了如上所說，D盤空間僅剩14.5G，並且tempdb的日誌檔案已經達到了130G。

登入到SQL專家雲，透過趨勢分析進行回溯，在 1 月2 2日上午 8 點40分之前，tempdb日誌檔案的總大小（藍線）一直保持在500M，使用空間（黃線）也能被重用。從這個時間點之後， 總空間和使用空間一直增長 。

分析 2

首先要了解一下tempdb日誌檔案重用的原理，因為tempdb的恢復模式是簡單的，所以只要對tempdb做完了checkpoint後，這個時間點之前的空間就可以重複使用了 。在SQL Server裡面，所有的日誌記錄都有嚴格順序，中間不可以有任何跳躍，如果某個時間點存在沒有提交的事務， 因為事務可能 會回滾，這些 日誌記錄都有可能需 要 被用來做回滾，因此SQL Server會標記從這個事務開始的 所 有日誌記錄（不管和這個事務有沒有關係）為活動事務日誌，導致日誌檔案不會被重用，只能是一直增長。

據此推測 在1月22日上午8點40分左右有一個或者多個會話有 沒有提交的事務，並且一直到現在為止都沒有提交。進入 SQL專家雲的 空閒會話 頁面， 點選 有未提交事務 選項卡，開始查詢這個時間段內的空閒會話，找到了ID為667的會話，空閒時間為16185分鐘，語句最後請求結束時間正好對應上Tempdb開始增長的時間點。

點選進入完整資訊，可以看到該會話在1月22日8點29分08秒建立的，在1月22日8點29分10秒開始了一個事務，在 1月 22日8點40分11秒後執行最後一條語句後不再執行語句，到目前為止該事務已經開了11天的時間。


解決 3

KILL掉這個會話，過幾分鐘後觀察日誌檔案的使用空間已經下降。

但是日誌檔案的總大小是不變的，再執行收縮tempdb日誌檔案的命令即可釋放掉磁碟的空間。

總結 4

這類問題的大多數原因是應用程式實現不嚴謹造成的，正常的流程下會提交事務，關閉資料庫連線，但是如果中間某個步驟出錯了，因為沒有異常處理，在這個出錯步驟後面的提交事務和關閉連線的程式碼都沒有執行到，最終導致 事務和連線的洩露。


所以根本的解決辦法是修改程式，因為客觀原因無法修改的，只能透過變通的方法來解決，例如在資料庫中建立一個 定期執行的作業，殺掉空閒時間長的會話。或者在SQL專家雲中啟用查殺會話的任務。

其它 5

很多客戶也碰到過這樣的現象， 日誌檔案 使用空間 一 直增長 ，很長時間內都不會下降， 確認過肯定 沒有未提交的事務 。 這是 因為tempdb的特殊性， 日誌檔案使用率超過 70% 才會觸發 checkpoint，重用的快慢取決於tempdb日誌檔案的大小。例如日誌檔案的總大小為100GB， 使用空間只有增長到70GB才會checkpoint，然後使用空間才會下降。所以不要把日誌檔案設定的太大。

北京格瑞趨勢科技有限公司是聚焦於資料服務的高新技術企業，成立於2008年，創始團隊及核心技術人員來自微軟和雅虎。微軟資料平臺金牌合作伙伴，衛寧健康資料平臺戰略合作伙伴。透過產品+服務雙輪驅動的業務模式，14年間累計服務4000+客戶，覆蓋網際網路、市政、交通、電信、醫療、教育、電力、製造業等各個領域。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70023658/viewspace-2937339/，如需轉載，請註明出處，否則將追究法律責任。

相關文章