Categories
Database Backup

** Discover expert strategies for preventing and resolving MSSQL transaction log full errors (Error 9002). Learn rapid recovery techniques, VLF management, and architectural best practices for DBAs.

對於管理 Microsoft SQL Server 的資料庫管理員 (DBA) 和 DevOps 工程師而言，很少有警報會像錯誤 9002：資料庫 ‘X’ 的交易記錄已滿 那樣引起立即的焦慮。當交易記錄填滿且無法擴充時，資料庫實際上會變為唯讀狀態。所有 INSERT、UPDATE 和 DELETE 作業都會停止，應用程式交易會失敗，生產環境也會陷入停滯。

了解 SQL Server 交易記錄的底層架構、準確診斷根本原因並執行快速復原程序，是維持高可用性的關鍵技能。本綜合指南將探討交易記錄的運作機制、如何在緊急情況下解決記錄已滿的問題，以及防止此類情況再次發生的架構最佳實踐。

了解 SQL Server 交易記錄架構

要有效排查交易記錄已滿的問題，您必須先了解 SQL Server 如何寫入和管理資料。

預寫式記錄 (Write-Ahead Logging, WAL)

SQL Server 使用預寫式記錄 (WAL) 協定。每當發生資料修改時，變更會先寫入記憶體中的交易記錄，然後在實際資料頁面於資料庫檔案 (MDF/NDF) 中更新之前，先刷新至磁碟上的實體記錄檔。這保證了 ACID（原子性、一致性、隔離性、持久性）合規性，確保在發生崩潰時，SQL Server 可以重播 (roll forward) 或復原 (roll back) 交易。

虛擬記錄檔 (VLFs) 與循環記錄

在內部，實體交易記錄檔 (LDF) 被劃分為稱為虛擬記錄檔 (VLF) 的較小邏輯區段。交易記錄以循環方式運作。當記錄寫入時，它們會填滿一個 VLF 並移動到下一個。

當記錄到達實體檔案末尾時，它會嘗試繞回到開頭。但是，只有在 VLF 被標記為非作用中 (inactive) 時，它才能覆寫該 VLF。如果所有 VLF 都是作用中的（意味著它們包含 SQL Server 仍需要的記錄），則記錄無法繞回。如果啟用了自動成長且磁碟空間可用，實體檔案會擴充。如果磁碟已滿或自動成長受到限制，您就會遇到錯誤 9002。

記錄截斷 (Truncation) 與記錄壓縮 (Shrinking)

一個常見的誤解是截斷記錄會減少實體檔案大小。
* 記錄截斷：將作用中 VLF 標記為非作用中的過程，使空間可用於重複使用。它不會減少磁碟上 LDF 檔案的大小。
* 記錄壓縮：物理上減少 LDF 檔案大小並將空間歸還給作業系統的過程。

在完整復原模式下，記錄截斷僅在成功完成交易記錄備份時才會發生（假設沒有其他程序保持記錄為作用中狀態）。

診斷「交易記錄已滿」錯誤 (錯誤 9002)

當記錄已滿時，您的第一步不應該是盲目地增加磁碟空間或壓縮檔案。您必須找出為什麼記錄無法截斷。SQL Server 提供了內建機制，透過 sys.databases 目錄檢視來告訴您確切是什麼原因阻止了記錄重複使用。

執行以下 T-SQL 指令來識別瓶頸：

SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel, 
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';

您也可以使用以下指令檢查交易記錄目前的空間使用情況：

DBCC SQLPERF(LOGSPACE);

常見的 log_reuse_wait_desc 狀態

  1. LOG_BACKUP：資料庫處於完整或大量記錄復原模式，且最近未進行交易記錄備份。這是最常見的原因。
  2. ACTIVE_TRANSACTION：長時間執行的交易（例如大規模索引重建或被遺忘的未提交交易）使記錄保持作用中。
  3. REPLICATION / CDC：啟用了交易式複寫或異動資料擷取 (CDC)，且記錄讀取器代理程式尚未處理這些交易。
  4. AVAILABILITY_REPLICA：在 AlwaysOn 可用性群組中，次要複本已中斷連線或同步速度過慢，迫使主要複本保留記錄，直到它們在次要複本上完成寫入。

快速復原策略：在生產環境中解決問題

根據傳回的 log_reuse_wait_desc，您的緊急應變措施會有所不同。以下是針對最常見情況的快速復原策略。

情境 1：遺失或失敗的記錄備份 (LOG_BACKUP)

如果等待類型為 LOG_BACKUP，解決方案很簡單：您必須備份交易記錄。

BACKUP LOG [YourDatabaseName] 
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn' 
WITH COMPRESSION, STATS = 10;

備份完成後，非作用中的 VLF 將被截斷，SQL Server 將恢復正常運作。如果您的備份磁碟已滿，您可能需要備份到臨時網路共用或 null 裝置（除非資料庫很容易重現，否則強烈不建議這樣做，因為這會中斷記錄鏈）：

-- 警告：這會中斷記錄鏈並損害時間點復原。
-- 僅在絕對必要時使用，並立即執行完整備份。
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

情境 2：長時間執行的作用中交易 (ACTIVE_TRANSACTION)

如果單一交易執行了數小時，它會導致整個期間無法截斷記錄。首先，識別有問題的交易：

DBCC OPENTRAN('YourDatabaseName');

此指令會傳回最舊的作用中交易及其伺服器處理程序 ID (SPID)。您可以透過查詢動態管理檢視 (DMV) 來收集有關該 SPID 正在執行什麼作業的更多詳細資訊：

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;

如果該交易是異常查詢或停滯的程序，您可能需要終止它以釋放記錄。

KILL <SPID>;

注意：終止大規模交易會觸發復原 (rollback)，這可能需要相當長的時間，並會暫時產生額外的記錄活動。在復原期間請勿重新啟動 SQL Server 服務，否則資料庫將在重新啟動時進入復原模式。

情境 3：緊急空間配置（磁碟 100% 已滿）

如果 LDF 檔案已耗盡整個磁碟機，您甚至無法執行備份，因為 SQL Server 需要極少量的記錄空間來記錄備份事件本身。在這種情況下，您必須在具有可用空間的不同磁碟機上新增次要記錄檔。

ALTER DATABASE [YourDatabaseName]
ADD LOG FILE 
(
    NAME = N'YourDatabaseName_Log2',
    FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
    SIZE = 5GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 1GB
);

這會立即為 SQL Server 提供喘息空間。一旦資料庫上線，請執行交易記錄備份，清空次要記錄檔，然後將其移除：

-- 1. 執行記錄備份以截斷記錄
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. 清空臨時記錄檔
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. 移除臨時記錄檔
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

交易記錄預防與管理的最佳實踐

被動排查問題壓力大且會影響 SLA。實施主動的架構與營運最佳實踐對於企業資料庫的穩定性至關重要。

1. 實施強大且自動化的備份策略

如果資料庫處於完整復原模式，則必須定期進行交易記錄備份。根據您的復原點目標 (RPO) 和交易量，記錄備份應每 5 到 15 分鐘執行一次。

像 CloudSave 這樣的企業級備份解決方案顯著簡化了此流程。透過 VDI (虛擬裝置介面) 直接與 SQL Server 整合，CloudSave 允許 DBA 設定策略驅動的高頻率交易記錄備份。這確保了記錄持續被截斷、安全加密並儲存在異地或不可變的雲端儲存中，從而防止 LOG_BACKUP 等待狀態，而無需複雜的自訂 SQL Agent 作業。

2. 調整交易記錄大小並管理 VLF

依賴自動成長來管理交易記錄大小是一種危險的反模式。自動成長作業成本高昂，並且在磁碟初始化為零時會暫停交易處理（除非啟用了即時檔案初始化，但這不適用於記錄檔）。

此外，頻繁且小幅度的自動成長（例如每次成長 10% 或 50MB）會導致 VLF 分散。擁有數千個微小 VLF 的交易記錄會嚴重降低資料庫啟動時間、備份效能和複寫延遲。

  • 預先設定記錄大小：分析您最大的維護作業（如索引重建），並預先設定 LDF 檔案大小以容納它們，而無需成長。
  • 設定固定自動成長：將自動成長從百分比變更為固定大小（例如 1GB 或 5GB），以確保 VLF 以適當的大小建立。

您可以使用以下查詢檢查您的 VLF 數量（適用於 SQL Server 2017+）：

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

如果您的 VLF 數量超過 500，請考慮在安靜時段，將記錄壓縮至最小大小，然後手動將其分批擴充回所需大小。

3. 優化索引維護作業

索引重建是完全記錄的作業，即使在大量記錄復原模式下也是如此（取決於索引類型）。重建 500GB 的索引至少會產生 500GB 的交易記錄。

為了減輕維護期間的記錄膨脹：
* 重建索引時使用 SORT_IN_TEMPDB = ON。這會將排序階段卸載到 TempDB，從而減輕使用者資料庫交易記錄的負擔。
* 盡可能從索引重建切換為索引重組，因為重組的記錄效率更高，且可以在不復原整個作業的情況下中斷。
* 批次處理大型 DELETE 或 UPDATE 作業。與其在一個交易中刪除 1000 萬行，不如以 50,000 行為一批進行刪除，並在批次之間提交，允許記錄備份截斷記錄。

4. 監控高可用性與複寫拓撲

在 AlwaysOn 可用性群組中，主要複本在記錄記錄已在所有同步和非同步次要複本上完成寫入之前，無法截斷其記錄。

如果次要複本離線，或者網路頻寬無法跟上主要複本的交易產生率，主要複本的傳送佇列將會增加，記錄將會填滿 (AVAILABILITY_REPLICA 等待類型)。

針對 SQLServer:Replica > Log Send Queue 效能計數器實施強大的監控。如果次要複本永久遺失，您必須將其從可用性群組中移除或暫停資料移動，以允許主要記錄進行截斷。

結論

遇到交易記錄已滿是資料庫管理員的必經之路，但這不一定會導致長時間的停機。透過了解預寫式記錄和 VLF 的機制，您可以使用 sys.databases 快速診斷根本原因並應用正確的快速復原策略。

長期穩定性取決於擺脫被動修復。預先設定記錄檔大小、優化維護常式，並利用像 CloudSave 這樣的企業級備份平台來執行嚴格、自動化的記錄備份排程，將確保您的交易記錄保持健康、已截斷，並隨時準備好支援高輸送量的生產工作負載。

分類