SQL Server 系統資料庫深度解析：master、model、msdb、tempdb、distribution 運作原理與維護要點

SQL Server 執行個體啟動、運作、管理、複寫、備份還原的核心中繼資料皆儲於**系統資料庫**。DBA 與開發人員深入理解其角色、內容、維護策略，是穩定營運的基石。

1. master：執行個體的大腦與心臟

• 核心職責：儲存執行個體級設定 (配置選項、連結伺服器、端點、憑證、非對稱金鑰)、所有資料庫的元資料 (檔案路徑、大小、狀態、擁有者)、登入帳號與伺服器角色成員、啟動預存程序。
• 關鍵物件：`sys.databases`、`sys.server_principals`、`sys.configurations`、`sys.linked_logins`、`sys.endpoints`、擴充預存程序 (`xp_cmdshell` 等)。
• 啟動相依性：SQL Server 服務啟動時**必須掛載 master**，否則啟動失敗。
• 維護鐵律：
  1. **每日完整備份** (含差異/記錄視 RPO)。
  2. **嚴禁直接修改系統表** (舊版 `sysdatabases` 等)，僅透過 T-SQL/SMO/PowerShell 管理。
  3. **記錄 master 檔案路徑、大小、配置輸出** (腳本化)，災難重建時依此重建。
  4. **掌握重建 master 流程** : 單一使用者模式啟動 → `sqlservr -m -f` → `RESTORE DATABASE master` → 重建登入/連結伺服器/配置。

2. model：使用者資料庫的藍圖範本

• 機制：`CREATE DATABASE` 時，**複製 model 內容** (結構、資料、權限、選項) 作為新資料庫初始狀態。
• 自訂內容：
  • 預設資料表/檢視/預存程序/函數 (如稽核觸發器範本)。
  • 預設檔案群組、檔案大小/成長設定。
  • 資料庫選項：`READ_COMMITTED_SNAPSHOT ON`、`PARAMETERIZATION FORCED`、`TRUSTWORTHY OFF` 等。
  • 使用者/角色/權限 (如建立 `db_ddladmin` 角色預設成員)。
• 影響範圍：`tempdb` 每次啟動也從 model 複製，**修改 model 會影響後續建立的所有資料庫與 tempdb**；謹慎測試後再部署。

3. tempdb：暫存物件的共用工作區

• 儲存內容：
  • 暫存表 (`#temp`、`##global_temp`)、資料表變數 (`@table`)、資料表值函數回傳集合。
  • 游標工作表、排序/雜湊/交換溢出工作表、版本存放區 (快照隔離、RCSI、觸發器 `inserted`/`deleted`、MARS、線上索引重建)。
  • 內部物件 (XML 變數剖析、Service Broker 訊息體、空間索引建構中繼結果)。
• 生命週期：服務啟動時**重新建立** (依 model 複製)，關閉時銷毀，**不保留任何資料跨啟動**。
• 效能優化關鍵：
  1. **多資料檔**：建議邏輯 CPU 核心數 ≤ 8 時，資料檔數 = 核心數；>8 時從 8 開始，觀察 `PAGE LATCH` 爭用調整。所有檔案**相同大小、相同成長量** (比例填滿演算法)。
  2. **預先成長**：啟動腳本預先 `ALTER DATABASE tempdb MODIFY FILE (SIZE = 50GB, FILEGROWTH = 5GB)`，避免自動成長阻塞。
  3. **獨立磁碟/儲存層**：tempdb I/O 極高，置於專用 SSD/NVMe/RAID 10，隔離使用者資料庫 I/O。
  4. **啟用 TF 1117/1118** (SQL 2016+ 預設行為)：統一範圍配置、混合範圍分配，減少 SGAM/PFS 爭用。
• 監控指標：`sys.dm_db_file_space_usage` (版本存放區/使用者/內部物件空間)、`sys.dm_db_session_space_usage`、`tempdb` 爭用等待 (`PAGELATCH_UP`、`PAGEIOLATCH`)。

4. msdb：Agent、備份、維護、複寫的中樞

• 核心內容：
  • SQL Server Agent 作業、步驟、排程、警示、操作員、歷程 (`sysjobhistory`)。
  • 備份/還原歷程 (`backupset`、`backupmediafamily`)、維護計畫套件/記錄/子計畫。
  • 複寫代理程式設定、發行集/訂閱集中繼資料 (若為散發者)。
  • SSIS 套件儲存 (舊版 `sysdtspackages`)、Database Mail 郵件/附件/記錄/設定檔。
  • 資料收集器 (Management Data Warehouse) 上傳資料。
• 維護要點：
  • **定期清除歷程** (`sp_purge_jobhistory`、`sp_delete_backuphistory`、`sp_maintplan_delete_log`)，防止 `msdb` 膨脹影響 Agent 效能。
  • **備份 msdb** (含於系統資料庫備份策略)，災難復原時需還原 Agent 作業與備份歷程。
  • **監控大小**：`msdb` 超過 10-20 GB 通常表示歷程清除失效，需立即處理。

5. distribution：複寫散發者的中繼資料倉庫

• 建立時機：設定「設定散發」精靈時自動建立 (預設名 `distribution`，可自訂)。
• 儲存內容：
  • 交易複寫：交易命令、追蹤權杖、衝突記錄 (合併複寫)。
  • 快照複寫：快照代理程式中繼資料、BCP 檔案位置。
  • 合併複寫：世代、衝突解決、發行項中繼資料。
  • 歷程清除作業 (`distribution_cleanup`、`distribution_cleanup: distribution`)。
• 關鍵維護：
  • **記錄檔別放在系統盤**，複寫高峰期寫入量大，獨立磁碟/檔案群組。
  • **監控未散發命令數** (`sp_replmonitorsubscriptionpendingcmds`)，積壓表示訂閱端延遲/故障。
  • **定期備份**，複寫拓撲重建需依賴散發資料庫中繼資料。

6. 資源資料庫 (Resource Database, mssqlsystemresource.mdf/ldf)

• 隱藏、唯讀資料庫，**包含所有系統物件定義** (`sys.objects`、`sys.columns` 等)，邏輯呈現於每個資料庫的 `sys` 結構描述。
• 位置：`\mssqlsystemresource.mdf`，不可備份/還原/附加/分離，升級/修補時自動替換。

總結：系統資料庫備份與監控策略表

深入掌握系統資料庫運作原理，是 DBA 從「會用」進階到「懂核心、能架構、會排查」的關鍵門檻。