SQL Server 資料庫檔案架構深度解析：MDF、NDF、LDF 角色與配置策略

三大檔案類型

檔案屬性五要素

建立/修改檔案時必須指定：

1. 邏輯名稱： 資料庫內部引用名（如 MyDB_Data）
2. 實體路徑： 作業系統完整路徑（如 E:\MSSQL\Data\MyDB.mdf）
3. 初始大小： 建立時分配空間，避免頻繁自動成長碎片化
4. 最大大小： 上限（MB/GB/TB 或 UNLIMITED）
5. 自動成長： 固定 MB 或百分比，**強烈建議固定 MB（如 256/512MB），禁用百分比**

預設值與建立行為

• CREATE DATABASE TestDB 最簡單語句下：
• .mdf 初始大小 = model 資料庫 .mdf 大小（預設 8MB）
• .ldf 初始大小 = 所有資料檔初始總和的 25%（最小 1MB）
• 自動成長：資料檔 64MB、記錄檔 10%（舊版）或 64MB（新版）

檔案群策略

CREATE DATABASE MyDB
ON PRIMARY (NAME=MyDB_Data, FILENAME='E:\Data\MyDB.mdf', SIZE=4GB, FILEGROWTH=512MB),
FILEGROUP [INDEXES] (NAME=MyDB_Idx, FILENAME='F:\Indexes\MyDB_Idx.ndf', SIZE=2GB, FILEGROWTH=512MB),
FILEGROUP [ARCHIVE] (NAME=MyDB_Arc, FILENAME='G:\Archive\MyDB_Arc.ndf', SIZE=10GB, FILEGROWTH=1GB)
LOG ON (NAME=MyDB_Log, FILENAME='L:\Logs\MyDB.ldf', SIZE=2GB, FILEGROWTH=512MB);

優勢：

• 將表/索引放入特定檔案群，實現熱/冷資料分離、不同儲存層級（SSD/HDD/雲端）
• 備份/還原可針對檔案群（BACKUP DATABASE ... FILEGROUP = 'ARCHIVE'）
• DBCC CHECKDB 可平行檢查多檔案群

記錄檔管理關鍵

• **簡單復原模式：** 檢查點自動截斷，空間循環使用
• **完整/大量記錄模式：** 必須定期 BACKUP LOG 截斷，否則無限增長
• 多記錄檔**不提升效能**（循序寫入），僅用於磁碟空間不足緊急擴容
• 監控 DBCC SQLPERF(LOGSPACE) 與 sys.dm_db_log_space_usage

常見誤區辨析

規劃清單

1. 依資料增長預測預分配空間（建議預留 2-3 倍）
2. 資料、索引、記錄分離不同磁碟/儲存層
3. 啟用瞬間檔案初始化（授權 Perform Volume Maintenance Tasks 給 SQL 服務帳號）
4. 設定固定自動成長、啟用最大檔案大小上限
5. 納入監控：檔案空間使用率、自動成長頻率、記錄檔截斷狀態