SQL Server 2005 變更 tempdb 資料庫檔案位置完整步驟

tempdb 是 SQL Server 的全域暫存資料庫，支援暫存表、表變數、游標、工作表、版本存放區等。將 tempdb 移至獨立磁碟/陣列可顯著降低 I/O 爭用，提升整體效能。本文說明標準離線移動流程。

第一步：確認目前檔案位置

SELECT name, physical_name 
FROM sys.master_files 
WHERE database_id = DB_ID(N'tempdb');

記錄回傳的 tempdev (資料檔) 與 templog (記錄檔) 實體路徑。

第二步：修改檔案路徑設定 (元資料更新)

USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\tempdb\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\tempdb\templog.ldf');
GO

關鍵點：此步驟僅更新系統目錄元資料，實體檔案不會移動，服務重啟前舊路徑仍生效。

第三步：手動移動實體檔案

1. 停止 SQL Server 服務 (MSSQLSERVER 或具名執行個體)
2. 將 tempdb.mdf 與 templog.ldf 從舊路徑剪貼 (移動) 至新路徑 (如 F:\tempdb\)
3. 確認目標資料夾授予 SQL Server 服務帳戶 完全控制 權限

第四步：重啟服務驗證

1. 啟動 SQL Server 服務
2. 再次執行第一步查詢，確認 physical_name 已更新為新路徑
3. 檢查錯誤記錄檔 無遺漏錯誤

最佳實踐補充

• 多資料檔：依 CPU 核心數建立 1~8 個 tempdb 資料檔 (同大小、同自動成長)，減少 SGAM/PFS 頁面爭用。
• 預先配置大小：設定合理初始大小與固定成長量 (如 512MB)，避免頻繁自動成長造成碎片與效能抖動。
• 啟用瞬間檔案初始化：授予服務帳戶 Perform volume maintenance tasks (SE_MANAGE_VOLUME_NAME) 權利，加速資料檔擴充。
• 獨立儲存：將 tempdb 放置於專用 NVMe/SSD/RAID 10 磁碟，與使用者資料庫、記錄檔、備份檔分離。

常見誤區

❌ 直接在 SSMS 圖形介面修改路徑後以為完成 — 必須手動移動檔案並重啟服務才生效。

❌ 使用 DETACH/ATTACH — tempdb 不支援分離/附加，僅能用 ALTER DATABASE ... MODIFY FILE。