淺談 SQL Server 的交易紀錄檔

資料庫檔案種類

在 SQL Server 中，建立資料庫時通常會產生兩個主要檔案：

• 主資料庫檔案（Master Database File）：

  副檔名為「.mdf」，這是 SQL 資料庫的核心檔案，包含資料庫的結構資訊（如資料表、索引、檢視表、預存程式）及所有使用者和系統資料。 交易記錄檔（Log Database File）：

• 副檔名為「.ldf」，用於記錄所有資料變更的交易記錄。即使資料庫發生故障，這些記錄可用於資料恢復，確保資料的一致性和完整性。

當進行資料庫操作時，資料變更首先會被記錄到交易記錄檔（.ldf）。當檢查點（Checkpoint）觸發時，SQL Server 會將所有已提交的交易變更從交易記錄檔（.ldf）中寫入主資料庫檔案（.mdf）。

復原模式

SQL Server 提供三種復原模式：

• 完整（Full Recovery Model）：

  • 所有的交易都會完整記錄在交易記錄檔中，包括插入、更新、刪除等所有操作。
  • 可以進行交易記錄檔的備份，並利用這些備份進行點時間還原（Point-in-Time Recovery），恢復到特定時間點的狀態。
  • 檢查點操作在這種模式下執行時，交易記錄檔不會自動截斷，必須通過定期備份來管理。

• 大量記錄（Bulk-Logged Recovery Model）：

  • 在執行批量操作（如大規模插入、更新、刪除或索引重建）時，使用最小資料記錄，以減少紀錄檔的大小和提高效能。
  • 單筆操作仍會維持正常記錄。
  • 檢查點操作不會自動截斷交易記錄檔。需要定期進行交易記錄檔備份來管理檔案的大小。
  • 只能恢復到批量操作前或後的時間點，無法進行細粒度的點時間還原。

• 簡單（Simple Recovery Model）：

  • 交易記錄檔在每次檢查點時都會自動截斷，這樣可以減少日誌檔的大小，但也意味著無法進行交易記錄檔的備份，且只能恢復到最後一次備份或資料庫的最近狀態。

交易記錄檔截斷是將交易記錄檔中的已提交交易記錄標記為可回收的過程，這樣 SQL Server 就能重用這些空間，進而避免交易記錄檔無限制地增長。

TIP

不管是哪種模式，都不影響 Begin Transaction 的使用。

備份模式

SQL Server 提供三種主要的備份方式：

• 完整備份（Full Backup）：
  • 完整備份會備份整個資料庫的所有內容，包括主資料庫檔案（.mdf）和交易記錄檔（.ldf）。
  • 完整備份可用於將資料庫還原到備份時的完整狀態。
• 差異備份（Differential Backup）：
  • 差異備份僅備份自上次完整備份以來發生變更的資料，從而節省備份所需的時間和空間。
  • 在還原時，需先還原最新的完整備份，然後再還原所有相關的差異備份，以恢復到特定的時間點。
• 交易紀錄檔備份（Transaction Log Backup）：
  • 交易紀錄檔備份會備份自上次交易紀錄檔備份以來的所有交易記錄。
  • 交易紀錄檔備份時會截斷交易紀錄檔中的已提交交易記錄，允許 SQL Server 重用這些空間，從而避免交易記錄檔無限制增長。
  • 進行交易紀錄檔備份前必須先執行完整備份。

在實務中，常見的備份策略是每週進行一次完整備份，每天一次差異備份，以及每 15 分鐘進行一次交易紀錄檔備份。具體頻率可以根據實際需求調整。

資料庫檔案的空間管理

資料庫檔案成長策略

建立資料庫時，可以設定以下檔案成長策略：

• 自動成長：
  • 檔案成長方式：
    • 以百分比為單位：每次成長的大小為目前檔案大小的百分比。
    • 以 MB 為單位：每次成長的大小為固定的 MB 數量。
  • 檔案大小上限：
    • 限制為 __（MB）：設定檔案的最大大小，超過此限制將不再自動成長。
    • 無限制：檔案可無限制擴展，直到磁碟空間用盡。
• 不自動成長： 若設定為不自動成長，檔案大小將不會自動擴增，需手動調整。

資料庫空間的種類

• 已設定的資料空間（Allocated Space）：指的是資料庫檔案（如 .mdf 和 .ldf）中已經分配的總空間，包括已使用和未使用的部分。這是檔案的總大小，即資料庫檔案在磁碟上的實際大小。
• 已使用的資料空間（Used Space）：指的是實際用來儲存資料的空間，包括資料表、索引、檢視表等所佔用的空間。這不包括空白或尚未使用的部分。

成長策略的影響

當資料庫的已使用空間超過已設定空間時，系統將根據成長策略自動增加已設定空間的大小。如果達到設定的檔案大小上限後，且沒有剩餘的未使用空間，資料庫將無法再寫入資料。

壓縮已設定空間

若需壓縮資料庫檔案的已設定空間，可使用以下指令來減少檔案的大小。邏輯名稱可透過 SQL Server Management Studio (SSMS) 資料庫屬性中的「檔案」頁面找到。

-- 將檔案大小收縮到 1000 MB
DBCC SHRINKFILE ({檔案邏輯名稱}, 1000);

注意事項：

• 壓縮的最小限制為已使用空間的大小。
• 若要有效壓縮交易紀錄檔，建議在執行 DBCC SHRINKFILE 前先進行交易紀錄檔備份，或將還原模式改為簡單，以觸發交易紀錄檔的截斷。
• 不建議對主資料檔案進行壓縮，這可能會導致交易紀錄檔暴增。

實務上常見問題

在實務中，常見的問題是資料庫選擇了完整復原模式，但卻未進行交易紀錄檔備份。這會帶來以下幾個主要問題：

• 交易紀錄檔無限制增長：

  若未進行交易紀錄檔備份，交易紀錄檔會持續增長，直到達到設定的空間大小，進而消耗大量硬碟空間。

• 備份檔案過大：

  完整備份會包含所有未截斷的交易紀錄，導致備份檔案龐大，增加備份所需的時間和空間。若有進行交易紀錄檔備份，完整備份將不包含這些已備份的交易紀錄，因此備份檔相對會較小。

• 備份策略失效：

  完整模式和大量紀錄模式的優勢在於能夠進行交易紀錄檔備份。如果未使用交易紀錄檔備份，還不如使用簡單模式，可能效能會更好一些。

異動歷程

• 2024-10-17 初版文件建立。