SQL Server TempDB 收縮操作完整指南：三種方法、適用場景與風險控管

TempDB 特性回顧

• 系統資料庫，每次 SQL Server 啟動時從 model 重建
• 儲存：暫存表 (#temp、##temp)、表變數、工作表 (排序、雜湊、游標)、版本存儲 (RCSI、Snapshot、觸發器)、內部物件
• 交易日誌僅記錄回滾資訊，無重做記錄，效能較高
• 預設自動成長，可能增長超過實際需求

三種收縮方法詳細比較

方法一：修改初始大小並重啟 (最乾淨、推薦)

-- 查看目前 TempDB 檔案配置
USE tempdb;
EXEC sp_helpfile;

-- 修改資料檔初始大小 (例如縮減至 500MB)
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 500MB);

-- 修改日誌檔初始大小 (例如 200MB)
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 200MB);

-- 重啟 SQL Server 服務生效
-- 服務管理器 / SQL Server Configuration Manager / net stop/start mssqlserver

重啟後 TempDB 將依新 SIZE 重建，舊檔案釋放。此方法可永久調整基準大小，避免反覆自動成長碎片化。

方法二：DBCC SHRINKDATABASE (整體收縮)

-- 目標壓縮至 30% 剩餘空間 (保留 70% 使用空間)
DBCC SHRINKDATABASE (tempdb, 30);

-- 或指定目標大小 (MB)
DBCC SHRINKDATABASE (tempdb, 1024);  -- 目標 1GB

⚠ 限制：執行期間 TempDB 不得有任何使用者活動 (含系統背景工作)。實務上極難滿足，常導致收縮失敗或卡住。建議僅在維護窗口、單用戶模式下嘗試。

方法三：DBCC SHRINKFILE (單檔收縮)

-- 收縮資料檔至 500MB
DBCC SHRINKFILE (tempdev, 500);

-- 收縮日誌檔至 200MB
DBCC SHRINKFILE (templog, 200);

-- 查看收縮進度
SELECT * FROM sys.dm_exec_requests WHERE command LIKE 'DBCC%';

同樣要求目標檔案期間無活動。多檔案 TempDB 時可針對特定檔案操作。

常見收縮失敗原因與對策

TempDB 容量規劃最佳實踐

1. 預先配置適當大小：依歷史峰值用量 + 20% 緩衝設定初始 SIZE，避免啟動後自動成長
2. 多資料檔案減少爭用：邏輯 CPU 核心數 ≤ 8 時，檔案數 = 核心數；> 8 時從 8 開始，依爭用情況增加 (最多 128)
3. 固定自動成長增量：FILEGROWTH = 512MB (而非 10%)，避免頻繁微幅成長
4. 啟用瞬間檔案初始化 (IFI)：SQL Server 服務帳戶加入「執行磁碟區維護工作」權限，資料檔成長零填充加速
5. 監控關鍵指標：
   • sys.dm_db_file_space_usage：版本存儲、內部物件、使用者物件空間
   • sys.dm_db_task_space_usage：工作階段層級配置/解配置
   • Performance Monitor: SQLServer:Transactions\Version Cleanup rate
6. 定期檢視錯誤日誌：自動成長事件、收縮失敗、空間不足警告

SQL Server 2005+ 管理工具差異

• SQL Server 2000：企業管理器 + 查詢分析器
• SQL Server 2005/2008/R2：SQL Server Management Studio (SSMS)
• SQL Server 2012+：SSMS (獨立安裝)、Azure Data Studio (跨平台)
• 所有版本 T-SQL 指令相容，圖形介面操作對應相同系统存儲程序

緊急情況快速處理流程

1. 確認磁碟空間不足警示來源確為 TempDB
2. 查詢 sys.dm_db_file_space_usage 判斷佔用類型 (版本存儲/暫存表/工作表)
3. 若為長時間交易/版本存儲：尋找並評估 KILL 阻塞 SPID (謹慎操作)
4. 若為暫存表濫用：聯繫開發團隊優化代碼、分批處理
5. 無法立即解決時：啟用自動成長 (若未啟用)、增加磁碟空間、排程重啟調整初始大小
6. 事後複盤：建立 TempDB 容量基準線、設定警示閾值 (如 80% 觸發告警)