共用方式為

交易記錄 (SQL Server)

  • 發行項

每個 SQL Server 資料庫都擁有交易記錄來記錄所有交易，以及交易在資料庫中所作的修改。 必須定期截斷交易記錄，以免被填滿。 但是，某些因素會影響記錄的截斷，所以監控記錄大小非常重要。 某些作業可使用最低限度記錄，以減少其對交易記錄大小的影響。

交易記錄是資料庫的重要元件，而且如果系統故障，就可能需要交易記錄讓資料庫返回一致的狀態。 永遠不要刪除或移動交易記錄，除非您完全了解執行這些動作的詳細情形。

注意

檢查點會在資料庫復原期間建立開始套用交易記錄的已知恰當起點。 如需詳細資訊，請參閱資料庫檢查點 (SQL Server)。

本主題內容：

優點：交易記錄所支援的作業

交易記錄檔支援下列作業：

  • 復原個別的交易。

  • 在 SQL Server 啟動時復原所有未完成的交易。

  • 將還原的資料庫、檔案、檔案群組或頁面向前復原到失敗點。

  • 支援異動複寫。

  • 支援高可用性和災害復原解決方案：Always On可用性群組、資料庫鏡像和記錄傳送。

交易記錄截斷

記錄截斷會釋出記錄檔中的空間，以供交易記錄重複使用。 為了避免記錄被填滿，必須截斷記錄。 記錄截斷會從SQL Server資料庫的邏輯交易記錄中刪除非作用中的虛擬記錄檔，釋放邏輯記錄中的空間供實體交易記錄重複使用。 如果永遠都不截斷交易記錄，最終將會填滿配置給其實體記錄檔的所有磁碟空間。

為了避免這個問題，除非記錄截斷因為某個原因而延遲，否則將在以下事件後自動進行截斷：

  • 在簡單復原模式下，發生在檢查點之後。

  • 在完整復原模式或大量記錄復原模式下，如果上一次備份之後已產生檢查點，則截斷會發生在記錄備份之後 (除非它是只複製的記錄備份)。

如需詳細資訊，請參閱本主題稍後 可能會延遲記錄截斷的因素。

注意

記錄截斷並不會讓實體記錄檔變小。 若要減少實體記錄檔的實體大小，您必須壓縮記錄檔。 如需有關壓縮實體記錄檔大小的詳細資訊，請參閱＜ 管理交易記錄檔的大小＞。

可能會延遲記錄截斷的因素

當記錄檔記錄有一段很長的時間維持在使用中狀態時，交易記錄截斷會延遲，而且可能會讓交易記錄填滿。

重要

如需如何因應完整交易記錄的相關資訊，請參閱對完整交易記錄進行疑難排解 (SQL Server 錯誤 9002)。

記錄截斷可能會因為各種因素而延遲。 若要探索是否有任何原因導致記錄截斷無法進行，請查詢 sys.databases 目錄檢視的 log_reuse_wait 和 log_reuse_wait_desc 資料行。 下表描述這些資料行的值。

log_reuse_wait 值 log_reuse_wait_desc 值 描述
0 NOTHING 目前有一個或多個可重複使用的虛擬記錄檔。
1 CHECKPOINT 自從上次記錄截斷後尚未出現任何檢查點，或是記錄標頭尚未移到虛擬記錄檔的範圍之外。 (所有復原模式)

這是延遲記錄截斷的一般原因。 如需詳細資訊，請參閱資料庫檢查點 (SQL Server)。
2 LOG_BACKUP 在截斷交易記錄前，需要進行記錄備份。 (僅限完整或大量記錄復原模式)

當下一個記錄備份完成後，某些記錄空間可能就可以重複使用。
3 ACTIVE_BACKUP_OR_RESTORE 正在進行資料備份或還原 (所有復原模式)。

如果資料備份阻礙截斷記錄，則取消備份作業可能有助於化解眼前的問題。
4 ACTIVE_TRANSACTION 交易在使用中 (所有復原模式)。

長時間執行的交易可能存在於記錄備份的開頭。 在此情況下，釋出空間可能需要另一個記錄備份。 請注意，長時間執行的交易會防止所有復原模式下的記錄截斷，包括簡單的復原模式，其中交易記錄通常會在每個自動檢查點上截斷。

延遲交易。 「延遲交易」 實際上是回復遭到封鎖的使用中交易 (因為某些無法使用的資源所造成)。 如需延遲交易的原因以及如何使延遲交易脫離延遲狀態的相關資訊，請參閱延遲交易 (SQL Server)。

長時間執行的交易可能也會填滿 tempdb 的交易記錄。 內部物件的使用者交易會隱含地使用 tempdb，例如進行排序的工作資料表、進行雜湊處理的工作檔案、資料指標工作資料表，以及資料列版本設定。 即使使用者交易只包含讀取資料 (SELECT 查詢) ，內建物件仍可在使用者交易下建立和使用。 因此，可能會填滿 tempdb 交易記錄。
5 DATABASE_MIRRORING 資料庫鏡像已暫停，或者在高效能模式下，鏡像資料庫已大幅落後主體資料庫。 (僅限完整復原模式)

如需詳細資訊，請參閱資料庫鏡像 (SQL Server)。
6 複寫 進行異動複寫期間，與發行集相關的交易仍然未傳遞至散發資料庫。 (僅限完整復原模式)

如需有關異動複寫的詳細資訊，請參閱＜ SQL Server Replication＞。
7 DATABASE_SNAPSHOT_CREATION 正在建立資料庫快照集。 (所有復原模式)

這是延遲記錄截斷的一般原因 (通常也是暫時的原因)。
8 LOG_SCAN 正在進行記錄掃描。 (所有復原模式)

這是延遲記錄截斷的一般原因 (通常也是暫時的原因)。
9 AVAILABILITY_REPLICA 可用性群組的次要複本正在將這個資料庫的交易記錄檔記錄套用到對應的次要資料庫。 (完整復原模式)

如需詳細資訊，請參閱AlwaysOn 可用性群組概觀 (SQL Server) 。
10 - 僅供內部使用
11 - 僅供內部使用
12 - 僅供內部使用
13 OLDEST_PAGE 如果將資料庫設定為使用間接檢查點，資料庫中最舊的頁面可能會比檢查點 LSN 更舊。 在此情況下，最舊的頁面可能會延遲記錄截斷。 (所有復原模式)

如需間接檢查點的相關資訊，請參閱資料庫檢查點 (SQL Server)。
14 OTHER_TRANSIENT 這個值目前尚未使用。
16 XTP_CHECKPOINT 當資料庫具有記憶體優化檔案群組時，交易記錄在觸發自動In-Memory OLTP 檢查點之前可能不會截斷， (每 512 MB 的記錄成長) 發生。

注意：若要在 512 MB 大小之前截斷交易記錄，請針對有問題的資料庫手動引發 Checkpoint 命令。

可以進行最低限度記錄的作業

「最低限度記錄」 包含僅記錄復原交易所需的資訊，不支援時間點復原。 這個主題將識別在大量記錄復原模式下 (以及簡單復原模式下，但備份正在執行時除外) 會進行最低限度記錄的作業。

注意

記憶體最佳化資料表不支援最低限度記錄。

注意

在完整復原模式下，將完整記錄所有大量作業。 不過，您可以暫時針對大量作業，將資料庫切換成大量記錄復原模式，藉以將大量作業集的記錄降至最低。 最低限度記錄會比完整記錄更具效率，並降低大規模的大量作業在大量交易期間，填滿可用交易記錄空間的可能性。 然而，如果資料庫在最低限度記錄作用時損毀或遺失，您就無法將資料庫復原至失敗點。

下列作業 (在完整復原模式下會完整記錄) 在簡單和大量記錄復原模式下會進行最低限度記錄：

  • 大量匯入作業 (bcp、BULK INSERT 及 INSERT...SELECT)。 如需何時大量匯入至資料表會採用最低限度記錄的詳細資訊，請參閱＜ Prerequisites for Minimal Logging in Bulk Import＞。

    注意

    啟用異動複寫時，即使在大量記錄復原模式下也會完整記錄 BULK INSERT 作業。

  • SELECT INTO 作業。

    注意

    啟用異動複寫時，即使在大量記錄復原模式下也會完整記錄 SELECT INTO 作業。

  • 插入或附加新資料時，在 UPDATE 陳述式中使用 .WRITE 子句，對大數值資料類型執行的部分更新。 請注意，更新現有值時不使用最低限度記錄。 如需關於大型值資料類型的詳細資訊，請參閱資料類型 (Transact-SQL)。

  • 將新資料插入或附加至 text 、 ntext 和 image 資料類型資料行時，WRITETEXT和UPDATETEXT語句。 請注意，更新現有值時不使用最低限度記錄。

    注意

    WRITETEXT 與 UPDATETEXT 陳述式已被取代，所以您應該避免在新的應用程式中使用它們。

  • 如果資料庫設定為簡單或大量記錄復原模式，則不管作業是離線執行或線上執行，某些索引 DDL 作業都是以最低限度的方式記錄。 以最低限度方式記錄的索引作業如下：

    • CREATE INDEX 作業 (包括索引檢視表)。

    • ALTER INDEX REBUILD 或 DBCC DBREINDEX 作業。

      注意

      DBCC DBREINDEX 陳述式已被取代，所以您應該避免在新的應用程式中使用它。

    • DROP INDEX 新堆積重建 (如果適用)。

      注意

      DROP INDEX 作業期間的索引頁取消配置永遠都是完整記錄。

相關工作

Managing the transaction log

備份交易記錄 (完整復原模式)

還原交易記錄 (完整復原模式)

另請參閱

控制交易持久性
大量匯入採用最低限度記錄的必要條件
SQL Server 資料庫的備份與還原
資料庫檢查點 (SQL Server)
檢視或變更資料庫的屬性
復原模式 (SQL Server)