前言

執行大量資料操作時，常遇到錯誤：訊息 9002，層級 17，狀態 2：資料庫 'tempdb' 的記錄檔已滿。請備份該資料庫的交易記錄檔以釋放一些記錄檔空間。 本文記錄真實排查過程，從症狀分析到根因修復，提供可落地的解決方案。

一、現象與初步判斷

• SQL 陳述式未使用臨時表，卻報 tempdb 日誌滿。
• 檢查 tempdb 屬性：自動增長啟用、無最大尺寸限制，磁碟剩餘空間充足。
• 嘗試 DUMP TRANSACTION tempdb WITH NO_LOG、BACKUP LOG tempdb WITH NO_LOG、收縮日誌 —— 均無效。

二、根因分析

1. 長時間交易或大批量操作：單一交易產生大量日誌，超過自動增長速度或觸及檔案系統上限。
2. 自動增長增量過小：預設 10% 或固定 MB，頻繁增長導致碎片與延遲，甚至來不及擴展。
3. 日誌無法截斷：tempdb 固定 Simple 模式，正常 Checkpoint 會截斷，但作用中交易會阻止截斷。

三、徹底修復步驟

1. 立即緩解：手動擴大資料/日誌檔初始大小

-- 將 tempdb 資料檔與日誌檔一次擴大至預估峰值 (例：3000 MB)
USE master;
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', SIZE = 3000MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'templog', SIZE = 3000MB);
GO

一次配置足夠空間，避免運行期自動增長抖動。

2. 優化自動增長設定

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'templog', FILEGROWTH = 512MB);  -- 固定增長，避免 10% 指數膨脹
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'templog', MAXSIZE = UNLIMITED);  -- 確保無上限

3. 多資料檔佈局（降低 PFS/GAM/SGAM 爭用）

-- 依 CPU 核心數建立多個資料檔，均勻分布
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = 'F:\MSSQL\Data\tempdev2.ndf', SIZE = 1000MB, FILEGROWTH = 512MB);
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = 'G:\MSSQL\Data\tempdev3.ndf', SIZE = 1000MB, FILEGROWTH = 512MB);
-- ... 以此類推

4. 程式碼層面預防

• 大批量操作分批提交（參考「TOP 子句分批更新」文章）。
• 避免在 tempdb 開啟長時間顯式交易。
• 臨時表/表變數用畢顯式 DROP/TRUNCATE，加速空間釋放。

四、緊急清理指令（僅限緊急介入）

-- 截斷日誌（Simple 模式下等同 CHECKPOINT + 收縮）
DBCC SHRINKFILE (N'templog', 0, TRUNCATEONLY);
-- 注意：頻繁收縮會產生碎片，平時禁止，僅限緊急

五、監控與告警

• 監控 sys.dm_db_log_space_usage 追蹤 tempdb 日誌使用率。
• 設定 Agent Alert：Database Log File Auto Growth 事件頻發時通知 DBA。
• 定期檢視 sys.dm_tran_active_transactions 找出長跑交易。

結語

tempdb 日誌滿本質是產生速度 > 增長/截斷速度。一次性擴大初始檔案、固定增長增量、多檔案佈局、分批提交業務交易，四管齊下可徹底根治。切記：收縮日誌只能救急，不能當常規手段。