交易記錄 (SQL Server)The Transaction Log (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

每個 SQL ServerSQL Server 資料庫都有交易記錄來記錄所有交易及每項交易所作的資料庫修改。Every SQL ServerSQL Server database has a transaction log that records all transactions and the database modifications made by each transaction.

交易記錄是資料庫的重要元件。The transaction log is a critical component of the database. 若系統故障,您便需要該記錄檔讓資料庫返回一致的狀態。If there is a system failure, you will need that log to bring your database back to a consistent state.

如需交易記錄架構與內部項目的資訊,請參閱 SQL Server 交易記錄架構與管理指南For information about the transaction log architecture and internals, see the SQL Server Transaction Log Architecture and Management Guide.

警告

永遠不要刪除或移動此記錄檔,除非您完全了解這麼做的後果。Never delete or move this log unless you fully understand the ramifications of doing so.

提示

檢查點會在資料庫復原期間建立開始套用交易記錄的已知恰當起點。Known good points from which to begin applying transaction logs during database recovery are created by checkpoints. 如需詳細資訊,請參閱資料庫檢查點 (SQL Server)For more information, see Database Checkpoints (SQL Server).

交易記錄所支援的作業Operations supported by the transaction log

交易記錄檔支援下列作業:The transaction log supports the following operations:

  • 個別交易的復原。Individual transaction recovery.
  • SQL ServerSQL Server 啟動時,復原所有未完成的交易。Recovery of all incomplete transactions when SQL ServerSQL Server is started.
  • 將還原的資料庫、檔案、檔案群組或頁面向前復原到失敗點。Rolling a restored database, file, filegroup, or page forward to the point of failure.
  • 支援異動複寫。Supporting transactional replication.
  • 支援高可用性和災害復原解決方案: AlwaysOn 可用性群組Always On availability groups、資料庫鏡像和記錄傳送。Supporting high availability and disaster recovery solutions: AlwaysOn 可用性群組Always On availability groups, database mirroring, and log shipping.

個別交易復原Individual transaction recovery

若應用程式發出一個 ROLLBACK 陳述式,或若是 Database EngineDatabase Engine 偵測到與用戶端的通訊中斷等錯誤,則記錄檔記錄可用來復原未完成交易所作的修改。If an application issues a ROLLBACK statement, or if the Database EngineDatabase Engine detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction.

SQL ServerSQL Server 啟動時復原所有未完成的交易Recovery of all incomplete transactions when SQL ServerSQL Server is started

如果伺服器失敗,資料庫的狀態可能停留於緩衝區快取中有些修改尚未寫入資料檔中,並且未完成的交易已在資料檔中作了一些修改。If a server fails, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. 當啟動 SQL ServerSQL Server 執行個體時,它會在每個資料庫上執行復原。When an instance of SQL ServerSQL Server is started, it runs a recovery of each database. 已記錄於記錄中、但尚未寫入資料檔中的每個修改將會向前復原。Every modification recorded in the log that may not have been written to the data files is rolled forward. 將會回復交易記錄檔中所發現的每筆未完成交易,以確保資料庫的完整性。Every incomplete transaction found in the transaction log is then rolled back to make sure the integrity of the database is preserved. 如需詳細資訊,請參閱還原和復原概觀 (SQL Server)For more information, see Restore and Recovery Overview (SQL Server).

將還原的資料庫、檔案、檔案群組或頁面向前復原到失敗點Rolling a restored database, file, filegroup, or page forward to the point of failure

在硬體損毀或磁碟失敗影響資料庫檔案後,您可以將資料庫還原至失敗點。After a hardware loss or disk failure affecting the database files, you can restore the database to the point of failure. 請先還原最後一次的完整資料庫備份和最後一次的差異資料庫備份,然後將後續一連串的交易記錄備份還原到失敗點。You first restore the last full database backup and the last differential database backup, and then restore the subsequent sequence of the transaction log backups to the point of failure.

在還原每個記錄備份時,Database EngineDatabase Engine 會重新套用記錄中的所有修改,以向前復原所有交易。As you restore each log backup, the Database EngineDatabase Engine reapplies all the modifications recorded in the log to roll forward all the transactions. 在還原最後一個記錄備份後,Database EngineDatabase Engine 接著會使用記錄資訊來復原在該點尚未完成的所有交易。When the last log backup is restored, the Database EngineDatabase Engine then uses the log information to roll back all transactions that were not complete at that point. 如需詳細資訊,請參閱還原和復原概觀 (SQL Server)For more information, see Restore and Recovery Overview (SQL Server).

支援異動複寫Supporting transactional replication

「記錄讀取器代理程式」會監視設定異動複寫的各資料庫交易記錄,並將標示要複寫的交易從交易記錄複製到散發資料庫中。The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database. 如需詳細資訊,請參閱 異動複寫的運作方式For more information, see How Transactional Replication Works.

支援高可用性和災害復原解決方案Supporting high availability and disaster recovery solutions

待命伺服器方案、AlwaysOn 可用性群組Always On availability groups、資料庫鏡像和記錄傳送都是高度依賴交易記錄。The standby-server solutions, AlwaysOn 可用性群組Always On availability groups, database mirroring, and log shipping, rely heavily on the transaction log.

AlwaysOn 可用性群組Always On availability groups 案例中,資料庫的每個更新 (主要複本) 都會立即在另一個完整的資料庫複本 (次要複本) 中重製。In an AlwaysOn 可用性群組Always On availability groups scenario, every update to a database, the primary replica, is immediately reproduced in separate, full copies of the database, the secondary replicas. 主要複本會立即將每個記錄檔記錄傳送至次要複本,而它會將收到的記錄檔記錄套用到可用性群組資料庫,以持續向前復原。The primary replica sends each log record immediately to the secondary replicas, that applies the incoming log records to availability group databases, continually rolling it forward. 如需詳細資訊,請參閱 Always On 容錯移轉叢集執行個體For more information, see Always On Failover Cluster Instances

記錄傳送案例中,主要伺服器會傳送主要資料庫的使用中交易記錄至一或多個目的地。In a log shipping scenario, the primary server sends the active transaction log of the primary database to one or more destinations. 每個次要伺服器都會將記錄檔還原至其本機次要資料庫。Each secondary server restores the log to its local secondary database. 如需詳細資訊,請參閱 關於記錄傳送For more information, see About Log Shipping.

資料庫鏡像案例中,主體資料庫的每個更新都會立即在另一個完整的個別資料庫複本 (鏡像資料庫) 中重製。In a database mirroring scenario, every update to a database, the principal database, is immediately reproduced in a separate, full copy of the database, the mirror database. 主體伺服器執行個體會立即傳送每個記錄檔記錄到鏡像伺服器執行個體,而它會將傳入的記錄檔記錄套用至鏡像資料庫,以持續向前復原。The principal server instance sends each log record immediately to the mirror server instance, which applies the incoming log records to the mirror database, continually rolling it forward. 如需詳細資訊,請參閱 資料庫鏡像For more information, see Database Mirroring.

交易記錄特性Transaction log characteristics

SQL Server Database EngineSQL Server Database Engine 交易記錄的特性:Characteristics of the SQL Server Database EngineSQL Server Database Engine transaction log:

  • 交易記錄是實作成資料庫中的個別檔案或一組檔案。The transaction log is implemented as a separate file or set of files in the database. 記錄檔快取是與資料頁的緩衝區快取分開管理,以在 SQL Server Database EngineSQL Server Database Engine 內產生簡單、快速和健全的程式碼。The log cache is managed separately from the buffer cache for data pages, which results in simple, fast, and robust code within the SQL Server Database EngineSQL Server Database Engine. 如需詳細資訊,請參閱交易記錄實體架構For more information, see Transaction Log Physical Architecture.

  • 記錄檔記錄與頁面的格式並不一定要遵照資料頁的格式。The format of log records and pages is not constrained to follow the format of data pages.

  • 交易記錄檔可實作於多個檔案上。The transaction log can be implemented in several files. 可以設定記錄檔的 FILEGROWTH 值,以定義記錄檔為自動擴充。The files can be defined to expand automatically by setting the FILEGROWTH value for the log. 這減少了交易記錄檔用完空間的可能性,而同時又減少了管理上的額外負擔。This reduces the potential of running out of space in the transaction log, while at the same time reducing administrative overhead. 如需詳細資訊,請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup Options.

  • 重複使用記錄檔空間的機制很迅速,並對於交易輸送量的影響很小。The mechanism to reuse the space within the log files is quick and has minimal effect on transaction throughput.

如需交易記錄架構與內部項目的資訊,請參閱 SQL Server 交易記錄架構與管理指南For information about the transaction log architecture and internals, see the SQL Server Transaction Log Architecture and Management Guide.

交易記錄截斷Transaction log truncation

記錄截斷會釋出記錄檔中的空間,以供交易記錄重複使用。Log truncation frees space in the log file for reuse by the transaction log. 您必須定期截斷交易記錄,以防止它填滿所分配的空間。You must regularly truncate your transaction log to keep it from filling the allotted space. 有數種因素會延遲記錄的截斷,所以監控記錄大小很重要。Several factors can delay log truncation, so monitoring log size matters. 某些作業可使用最低限度記錄,以減少其對交易記錄大小的影響。Some operations can be minimally logged to reduce their impact on transaction log size.

記錄截斷會從 SQL ServerSQL Server 資料庫的邏輯交易記錄中刪除非使用中的虛擬記錄檔 (VLF),釋出邏輯記錄中的空間以供實體交易記錄重複使用。Log truncation deletes inactive virtual log files (VLFs) from the logical transaction log of a SQL ServerSQL Server database, freeing space in the logical log for reuse by the Physical transaction log. 如果永遠都不截斷交易記錄,最終將會填滿分配給實體記錄檔的所有磁碟空間。If a transaction log is never truncated, it will eventually fill all the disk space allocated to physical log files.

為了避免用盡空間,除非記錄截斷因為某個原因而延遲,否則將在以下事件後自動進行截斷:To avoid running out of space, unless log truncation is delayed for some reason, truncation occurs automatically after the following events:

  • 在簡單復原模式下,發生在檢查點之後。Under the simple recovery model, after a checkpoint.
  • 在完整復原模式或大量記錄復原模式下,如果上一次備份之後已產生檢查點,則截斷會發生在記錄備份之後 (除非它是只複製的記錄備份)。Under the full recovery model or bulk-logged recovery model, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).

如需詳細資訊,請參閱本主題稍後的可能會延遲記錄截斷的因素For more information, see Factors that can delay log truncation, later in this topic.

注意

記錄截斷並不會讓實體記錄檔變小。Log truncation does not reduce the size of the physical log file. 若要減少實體記錄檔的實體大小,則必須壓縮記錄檔。To reduce the physical size of a physical log file, you must shrink the log file. 如需有關壓縮實體記錄檔大小的詳細資訊,請參閱< 管理交易記錄檔的大小>。For information about shrinking the size of the physical log file, see Manage the Size of the Transaction Log File.
不過,請記住可能會延遲記錄截斷的因素However, keep in mind Factors that can delay log truncation. 如果壓縮記錄檔之後再次需要儲存空間,交易記錄檔將再次成長,並且會因此在記錄檔成長作業期間,導入效能額外負荷。If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log grow operations.

Factors that can delay log truncationFactors that can delay log truncation

當記錄檔記錄有一段很長的時間維持在使用中狀態時,交易記錄截斷會延遲,並填滿交易記錄,就像我們在這個長主題前面所提的一樣。When log records remain active for a long time, transaction log truncation is delayed, and the transaction log can fill up, as we mentioned earlier in this long topic.

重要

如需如何對應寫滿交易記錄的相關資訊,請參閱 Troubleshoot a Full Transaction Log (SQL Server Error 9002)For information about how to respond to a full transaction log, see Troubleshoot a Full Transaction Log (SQL Server Error 9002).

實際上,記錄截斷可能會因為各種原因而延遲。Really, Log truncation can be delayed by a variety of reasons. 查詢 sys.databases 目錄檢視的 log_reuse_waitlog_reuse_wait_desc 資料行,了解是否有任何原因導致無法進行記錄截斷。Learn what, if anything, is preventing your log truncation by querying the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view. 下表描述這些資料行的值。The following table describes the values of these columns.

log_reuse_wait 值log_reuse_wait value log_reuse_wait_desc 值log_reuse_wait_desc value DescriptionDescription
00 NOTHINGNOTHING 目前有一或多個可重複使用的虛擬記錄檔 (VLF)Currently there are one or more reusable virtual log files (VLFs).
11 CHECKPOINTCHECKPOINT 自從上次記錄截斷後尚未出現任何檢查點,或是記錄標頭尚未移到虛擬記錄檔 (VLF) 的範圍之外。No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (VLF). (所有復原模式)(All recovery models)

這是延遲記錄截斷的一般原因。This is a routine reason for delaying log truncation. 如需詳細資訊,請參閱資料庫檢查點 (SQL Server)For more information, see Database Checkpoints (SQL Server).
22 LOG_BACKUPLOG_BACKUP 在截斷交易記錄前,需要進行記錄備份。A log backup is required before the transaction log can be truncated. (僅限完整或大量記錄復原模式)(Full or bulk-logged recovery models only)

當下一個記錄備份完成後,某些記錄空間可能就可以重複使用。When the next log backup is completed, some log space might become reusable.
33 ACTIVE_BACKUP_OR_RESTOREACTIVE_BACKUP_OR_RESTORE 正在進行資料備份或還原 (所有復原模式)。A data backup or a restore is in progress (all recovery models).

如果資料備份阻礙截斷記錄,則取消備份作業可能有助於化解眼前的問題。If a data backup is preventing log truncation, canceling the backup operation might help the immediate problem.
44 ACTIVE_TRANSACTIONACTIVE_TRANSACTION 交易在使用中 (所有復原模式):A transaction is active (all recovery models):

長時間執行的交易可能存在於記錄備份的開頭。A long-running transaction might exist at the start of the log backup. 在此情況下,釋出空間可能需要另一個記錄備份。In this case, freeing the space might require another log backup. 請注意,長時間執行的交易會避免所有復原模式下的記錄截斷,包括簡單復原模式,在該模式下,通常會在每一個自動檢查點截斷交易記錄。Note that long-running transactions prevent log truncation under all recovery models, including the simple recovery model, under which the transaction log is generally truncated on each automatic checkpoint.

延遲交易。A transaction is deferred. 「延遲交易」 實際上是回復遭到封鎖的使用中交易 (因為某些無法使用的資源所造成)。A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. 如需有關延遲交易的原因以及如何將延遲交易移出延遲狀態的詳細資訊,請參閱延遲交易 (SQL Server)For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions (SQL Server).

長時間執行的交易可能也會填滿 tempdb 的交易記錄。Long-running transactions might also fill up tempdb's transaction log. 內部物件的使用者交易會隱含地使用 tempdb,例如進行排序的工作資料表、進行雜湊處理的工作檔案、資料指標工作資料表,以及資料列版本設定。Tempdb is used implicitly by user transactions for internal objects such as work tables for sorting, work files for hashing, cursor work tables, and row versioning. 即使使用者交易只包括讀取資料 (SELECT 查詢),還是可以在使用者交易下建立和使用內部物件。Even if the user transaction includes only reading data (SELECT queries), internal objects may be created and used under user transactions. 因此,可能會填滿 tempdb 交易記錄。Then the tempdb transaction log can be filled.
55 DATABASE_MIRRORINGDATABASE_MIRRORING 資料庫鏡像已暫停,或者在高效能模式下,鏡像資料庫已大幅落後主體資料庫。Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (僅限完整復原模式)(Full recovery model only)

如需詳細資訊,請參閱資料庫鏡像 (SQL Server)For more information, see Database Mirroring (SQL Server).
66 REPLICATIONREPLICATION 進行異動複寫期間,與發行集相關的交易仍然未傳遞至散發資料庫。During transactional replications, transactions relevant to the publications are still undelivered to the distribution database. (僅限完整復原模式)(Full recovery model only)

如需有關異動複寫的詳細資訊,請參閱< SQL Server Replication>。For information about transactional replication, see SQL Server Replication.
77 DATABASE_SNAPSHOT_CREATIONDATABASE_SNAPSHOT_CREATION 正在建立資料庫快照集。A database snapshot is being created. (所有復原模式)(All recovery models)

這是延遲記錄截斷的一般原因 (通常也是暫時的原因)。This is a routine, and typically brief, cause of delayed log truncation.
88 LOG_SCANLOG_SCAN 正在進行記錄掃描。A log scan is occurring. (所有復原模式)(All recovery models)

這是延遲記錄截斷的一般原因 (通常也是暫時的原因)。This is a routine, and typically brief, cause of delayed log truncation.
99 AVAILABILITY_REPLICAAVAILABILITY_REPLICA 可用性群組的次要複本正在將這個資料庫的交易記錄檔記錄套用到對應的次要資料庫。A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (完整復原模式)(Full recovery model)

如需詳細資訊,請參閱 AlwaysOn 可用性群組概觀 (SQL Server)For more information, see Overview of Always On Availability Groups (SQL Server).
1010 - 僅供內部使用For internal use only
1111 - 僅供內部使用For internal use only
1212 - 僅供內部使用For internal use only
1313 OLDEST_PAGEOLDEST_PAGE 如果將資料庫設定為使用間接檢查點,資料庫中最舊的頁面可能會比檢查點記錄序號 (LSN) 更舊。If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN). 在此情況下,最舊的頁面可能會延遲記錄截斷。In this case, the oldest page can delay log truncation. (所有復原模式)(All recovery models)

如需間接檢查點的相關資訊,請參閱 Database Checkpoints (SQL Server)For information about indirect checkpoints, see Database Checkpoints (SQL Server).
1414 OTHER_TRANSIENTOTHER_TRANSIENT 這個值目前尚未使用。This value is currently not used.

可以進行最低限度記錄的作業Operations that can be minimally logged

「最低限度記錄」 包含僅記錄復原交易所需的資訊,不支援時間點復原。Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery. 這個主題將識別在大量記錄 復原模式 下 (以及簡單復原模式下,但備份正在執行時除外) 會進行最低限度記錄的作業。This topic identifies the operations that are minimally logged under the bulk-logged recovery model (as well as under the simple recovery model, except when a backup is running).

注意

記憶體最佳化資料表不支援最低限度記錄。Minimal logging is not supported for memory-optimized tables.

注意

在完整 復原模式下,將完整記錄所有大量作業。Under the full recovery model, all bulk operations are fully logged. 不過,您可以暫時針對大量作業,將資料庫切換成大量記錄復原模式,藉以將大量作業集的記錄降至最低。However, you can minimize logging for a set of bulk operations by switching the database to the bulk-logged recovery model temporarily for bulk operations. 最低限度記錄會比完整記錄更具效率,並降低大規模的大量作業在大量交易期間,填滿可用交易記錄空間的可能性。Minimal logging is more efficient than full logging, and it reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction. 然而,如果資料庫在最低限度記錄作用時損毀或遺失,您就無法將資料庫復原至失敗點。However, if the database is damaged or lost when minimal logging is in effect, you cannot recover the database to the point of failure.

下列作業 (在完整復原模式下會完整記錄) 在簡單和大量記錄復原模式下會進行最低限度記錄:The following operations, which are fully logged under the full recovery model, are minimally logged under the simple and bulk-logged recovery model:

啟用異動複寫時,即使在大量記錄復原模式下也會完整記錄 BULK INSERT作業。When transactional replication is enabled, BULK INSERT operations are fully logged even under the Bulk Logged recovery model.

啟用異動複寫時,即使在大量記錄復原模式下也會完整記錄 SELECT INTO作業。When transactional replication is enabled, SELECT INTO operations are fully logged even under the Bulk Logged recovery model.

  • 插入或附加新資料時,在 UPDATE 陳述式中使用 .WRITE 子句,對大數值資料類型執行的部分更新。Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data. 請注意,更新現有值時不使用最低限度記錄。Note that minimal logging is not used when existing values are updated. 如需有關大數值資料類型的詳細資訊,請參閱資料類型 (Transact-SQL)For more information about large value data types, see Data Types (Transact-SQL).

  • 將新的資料插入或附加至UPDATETEXTnUPDATETEXTUPDATETEXT, nUPDATETEXT, 、 UPDATETEXT 陳述式。WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns. 請注意,更新現有值時不使用最低限度記錄。Note that minimal logging is not used when existing values are updated.

    警告

    WRITETEXTUPDATETEXT 陳述式已被取代,所以您應該避免在新的應用程式中加以使用。The WRITETEXT and UPDATETEXT statements are deprecated; avoid using them in new applications.

  • 如果資料庫設定為簡單或大量記錄復原模式,則不管作業是離線執行或線上執行,某些索引 DDL 作業都是以最低限度的方式記錄。If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online. 以最低限度方式記錄的索引作業如下:The minimally logged index operations are as follows:

    • CREATE INDEX 作業 (包括索引檢視表)。CREATE INDEX operations (including indexed views).

    • ALTER INDEX REBUILD 或 DBCC DBREINDEX 作業。ALTER INDEX REBUILD or DBCC DBREINDEX operations.

      警告

      DBCC DBREINDEX 陳述式已被淘汰;請避免在新的應用程式中使用。The DBCC DBREINDEX statement is deprecated; Do not use it in new applications.

    • DROP INDEX 新堆積重建 (如果適用)。DROP INDEX new heap rebuild (if applicable). DROP INDEX作業期間的索引頁取消配置一律經完整記錄。Index page deallocation during a DROP INDEX operation is always fully logged.

Related tasksRelated tasks

管理交易記錄Managing the transaction log

備份交易記錄 (完整復原模式)Backing Up the Transaction Log (Full Recovery Model)

還原交易記錄 (完整復原模式)Restoring the Transaction Log (Full Recovery Model)

另請參閱See also

SQL Server 交易記錄架構與管理指南 SQL Server Transaction Log Architecture and Management Guide
控制交易持久性 Control Transaction Durability
大量匯入採用最低限度記錄的必要條件 Prerequisites for Minimal Logging in Bulk Import
SQL Server 資料庫的備份與還原 Back Up and Restore of SQL Server Databases
還原和復原概觀 (SQL Server) Restore and Recovery Overview (SQL Server)
資料庫檢查點 (SQL Server) Database Checkpoints (SQL Server)
檢視或變更資料庫的屬性 View or Change the Properties of a Database
復原模式 (SQL Server)Recovery Models (SQL Server)
交易記錄備份 (SQL Server) Transaction Log Backups (SQL Server)
sys.dm_db_log_info (Transact-SQL)sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL)sys.dm_db_log_space_usage (Transact-SQL)