SQL Server「tempdb 日誌已滿」錯誤解決方案：擴充日誌檔案與清理命令實戰

執行長時間查詢/大量操作導致 tempdb 交易日誌填滿，報錯 9002。本文記錄實測有效的解決路徑：擴充日誌檔案初始大小與成長上限、清空日誌命令、收縮操作，並說明為何單純清空日誌無法根治。

錯誤訊息

伺服器: 訊息 9002，層級 17，狀態 2，行 1
資料庫 'tempdb' 的記錄已滿。請備份該資料庫的交易記錄以釋放一些記錄空間。

環境狀態

• 查詢未使用臨時表，但涉及大量排序/雜湊/版本存放區操作。
• 日誌檔設定為自動成長、無上限 (MAXSIZE UNLIMITED)。
• 磁碟剩餘空間充足，但日誌成長速度超過磁碟寫入/擴展速度。

解決方案：擴充日誌與資料檔案初始大小 (根治)

-- 1. 設定日誌檔無上限成長 (確認)
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'templog', MAXSIZE = UNLIMITED);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'templog', FILEGROWTH = 10%);

-- 2. 關鍵：預先配置大尺寸初始空間 (如 1000MB = 1GB)
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'templog', SIZE = 1000);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', SIZE = 1000);

實測： 將初始大小從預設 8MB 直接調至 1000MB，問題即時解決，後續操作無日誌成長延遲。

輔助命令：清空/收縮日誌 (臨時緩解)

-- 清空日誌 (舊版語法，2005+ 建議用 BACKUP LOG ... WITH NO_LOG 替代)
DUMP TRANSACTION tempdb WITH NO_LOG;

-- 截斷交易日誌 (同義)
BACKUP LOG tempdb WITH NO_LOG;

-- 收縮日誌檔案 (SSMS 圖形介面：右鍵資料庫 → 所有工作 → 收縮 → 檔案 → 選擇 templog)
DBCC SHRINKFILE (N'templog', 0, TRUNCATEONLY);

警告： 單純清空/收縮日誌無法根治，若負載持續，日誌會迅速再次填滿。必須配合「預先配置大尺寸初始空間」才能穩定。

為什麼會發生在 tempdb？

• tempdb 採 Simple 復原模式，正常情況檢查點會截斷日誌。
• 長時間運行的交易 (顯式/隱式) 阻礙日誌截斷：大量排序、雜湊聯接、版本存放區清理延遲。
• 自動成長 10% 在大檔案時單次成長量巨大 (如 100GB 成長 10GB)，導致 I/O 停頓與空間壓力。

最佳實務建議

1. 預先配置：依歷史峰值佔用設定初始大小 (資料檔 + 日誌檔)，避免自動成長。
2. 固定成長量：生產環境建議 FILEGROWTH = 512MB 或 1024MB，不使用百分比。
3. 多日誌檔無效：tempdb 僅支援單一記錄檔，多資料檔僅適用資料檔。
4. 監控腳本：定期檢查 DBCC SQLPERF(LOGSPACE) 關注 tempdb 日誌使用率 > 70% 即預警。
5. 避免長交易：分批處理大量資料、優化排序/雜湊運算記憶體授予。

tempdb 日誌滿是「空間預留不足」與「長交易阻礙截斷」的雙重結果，預先配置大尺寸初始空間是最高 ROI 的解決方案。