// SQL Server 錯誤參考

SQL Server 錯誤 9002:資料庫的交易記錄已滿

資料庫的記錄檔已滿且無法成長,因此寫入失敗。訊息中標示的原因(LOG_BACKUP、ACTIVE_TRANSACTION...)說明了是什麼阻止記錄被重複使用。

錯誤號碼
9002
嚴重性
17
類別
記憶體、記錄與儲存
錯誤訊息
The transaction log for database '%ls' is full due to '%ls' and the holdup lsn is %S_LSN.

它的意義

SQL Server 會先把每個變更寫入交易記錄,然後才變更資料。記錄空間要等到確定沒有任何東西需要它時才會重複使用:在完整復原模式下,是指記錄備份之後;而在所有模式下,最舊的未結束交易、複寫或可用性群組複本都可能把它佔住。如果記錄無法重複使用,也無法成長(達到大小上限或磁碟已滿),每次寫入都會失敗並出現 9002。

訊息會指出原因,其值與 log_reuse_wait_desc 在 sys.databases.

常見原因

  • LOG_BACKUP:資料庫處於完整或大量記錄復原模式,且沒有在進行記錄備份。
  • ACTIVE_TRANSACTION: 長時間執行或被遺忘的開啟交易鎖住了記錄。
  • REPLICATION 或 AVAILABILITY_REPLICA: 記錄讀取器或次要複本落後。
  • 記錄檔有固定的大小上限,或者存放記錄檔的磁碟已滿。
  • 單一筆超大交易,例如一個陳述式刪掉數百萬筆資料列。

如何修正

  1. 查看記錄檔為何無法重複使用。

    SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = N'SalesDb';
  2. 對於 LOG_BACKUP,進行記錄備份(並安排其排程)。

    BACKUP LOG SalesDb TO DISK = N'D:\Backup\SalesDb_log.trn';
  3. 對於 ACTIVE_TRANSACTION,先把這件事處理掉。

    DBCC OPENTRAN (N'SalesDb');
  4. 給記錄檔留出空間:讓它成長,或在另一顆磁碟上新增第二個記錄檔。

    ALTER DATABASE SalesDb MODIFY FILE (NAME = SalesDb_log, MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);
  5. 將龐大的資料變更分批處理,讓每個交易保持精簡。

    WHILE 1 = 1
    BEGIN
        DELETE TOP (10000) FROM dbo.EventLog WHERE CreatedAt < '2024-01-01';
        IF @@ROWCOUNT = 0 BREAK;
    END;
  6. 如果這個資料庫不需要時間點還原,簡單復原模式會在每次檢查點之後重複使用記錄空間。

    ALTER DATABASE SalesDb SET RECOVERY SIMPLE;

問題

修復錯誤 9002 之後,我該縮小記錄檔嗎?

只有在它因為一次性事件而暴增、遠超過正常大小時才需要。如果記錄檔平常就需要這麼大的空間,壓縮它只會讓它再長回來,寫入還得一邊等待,白費時間。

相關錯誤

更多記憶體、記錄和儲存錯誤

程式碼和訊息文字來自 Microsoft 資料庫引擎事件和錯誤參考。瀏覽完整的 SQL Server 錯誤清單 或 搜尋每個資料庫錯誤.

// 錯誤更少,修復更快

在 SQLly 裡操作 SQL Server

適用於 macOS、Windows 和 Linux 的原生 SQL 客戶端,具備結構描述感知的 IntelliSense、高風險陳述式防護與清楚的錯誤訊息,適用於 SQL Server 和你使用的其他資料庫。