SQL Server 交易記錄架構與管理指南SQL Server Transaction Log Architecture and Management Guide

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

每個 SQL ServerSQL Server 資料庫都有交易記錄檔,這個記錄檔會記錄所有交易,以及個別交易在資料庫中所做的修改。Every SQL ServerSQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. 交易記錄是資料庫的重要元件,而且如果系統故障,就可能需要交易記錄讓資料庫返回一致的狀態。The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. 本指南提供有關交易記錄實體及邏輯架構的資訊。This guide provides information about the physical and logical architecture of the transaction log. 了解此架構可提升交易記錄管理的效能。Understanding the architecture can improve your effectiveness in managing transaction logs.

交易記錄邏輯架構Transaction Log Logical Architecture

在邏輯上, SQL ServerSQL Server 交易記錄檔會以記錄檔記錄字串的形式進行運作。The SQL ServerSQL Server transaction log operates logically as if the transaction log is a string of log records. 每個記錄檔記錄均由記錄序號 (LSN) 來識別。Each log record is identified by a log sequence number (LSN). 每筆新記錄檔記錄都會寫入記錄檔的邏輯結尾處,並且其 LSN 比它前一個記錄的 LSN 來得大。Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it. 記錄檔記錄將在它們建立時依序儲存。Log records are stored in a serial sequence as they are created. 每筆記錄檔記錄都包含所屬交易的識別碼。Each log record contains the ID of the transaction that it belongs to. 對於每筆交易,所有與交易關聯的記錄檔記錄將使用反向指標個別地連結於鏈結之中,以加速交易的回復。For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

資料修改的記錄檔記錄可記錄所執行的邏輯作業,或是已修改資料的前置與後置資料影像。Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. 前置資料影像為執行作業之前的資料副本;後置資料影像為執行作業之後的資料副本。The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

復原作業的步驟取決於記錄檔記錄的類型:The steps to recover an operation depend on the type of log record:

  • 記錄的邏輯作業Logical operation logged

    • 若要向前復原邏輯作業,必須再次執行作業。To roll the logical operation forward, the operation is performed again.

    • 若要回復邏輯作業,則執行反向的邏輯作業。To roll the logical operation back, the reverse logical operation is performed.

  • 記錄的前置與後置資料影像Before and after image logged

    • 若要向前復原作業,將套用後置資料影像。To roll the operation forward, the after image is applied.

    • 若要回復作業,將套用前置資料影像。To roll the operation back, the before image is applied.

交易記錄檔中記錄了許多類型的作業。Many types of operations are recorded in the transaction log. 這些作業包括:These operations include:

  • 每筆交易的開始與結束。The start and end of each transaction.

  • 每個資料修改 (插入、更新或刪除)。Every data modification (insert, update, or delete). 這包括系統預存程序或資料定義語言 (DDL) 陳述式對任何資料表 (包括系統資料表) 所做的變更。This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

  • 每個範圍與分頁的配置或取消配置。Every extent and page allocation or deallocation.

  • 建立或卸除資料表或索引。Creating or dropping a table or index.

回復作業也會留下記錄。Rollback operations are also logged. 每筆交易都會在交易記錄檔中保留空間,以確保有足夠的記錄檔空間可支援由明確回復陳述式所造成的回復,或因發生錯誤而造成的回復。Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. 保留的空間大小須視交易中執行的作業而定,但通常會等於用來記錄每個作業的空間大小。The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. 當交易完成後就會釋放這個保留空間。This reserved space is freed when the transaction is completed.

在記錄檔中,從對成功回復全資料庫而言不可或缺的第一筆記錄檔記錄,一直到最後寫入的記錄檔記錄的這個區段,稱為記錄檔的使用中部分,或「使用中的記錄」 。The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. 這是需要進行完整資料庫復原的記錄區段。This is the section of the log required to a full recovery of the database. 沒有任何使用中的記錄部分可被截斷。No part of the active log can ever be truncated. 此第一個記錄檔記錄的記錄序號 (LSN) 就稱為最小復原 LSN (MinLSN)The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN).

交易記錄實體架構Transaction Log Physical Architecture

資料庫中的交易記錄會對應到一個或多個實體檔案。The transaction log in a database maps over one or more physical files. 從概念上來說,記錄檔是記錄的字串。Conceptually, the log file is a string of log records. 就實際上來說,記錄的順序必須有效地儲存在實作交易記錄的一組實體檔案中。Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. 每個資料庫至少要有一個記錄檔。There must be at least one log file for each database.

SQL Server Database EngineSQL Server Database Engine 會在內部將每個實體記錄檔分成數個虛擬記錄檔 (VLF)。The SQL Server Database EngineSQL Server Database Engine divides each physical log file internally into a number of virtual log files (VLFs). 虛擬記錄檔沒有固定的大小,一個實體記錄檔也沒有固定的虛擬記錄檔數目。Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. Database EngineDatabase Engine 在建立或擴充記錄檔時,會動態選擇虛擬記錄檔的大小。The Database EngineDatabase Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. Database EngineDatabase Engine 會盡量維持少量的虛擬檔。The Database EngineDatabase Engine tries to maintain a small number of virtual files. 記錄檔擴充之後的虛擬檔大小,是現有記錄檔大小以及新檔案所增加的大小總和。The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. 系統管理員無法設定虛擬記錄檔的大小或數目。The size or number of virtual log files cannot be configured or set by administrators.

注意

虛擬記錄檔 (VLF) 建立遵循此方法:Virtual log file (VLF) creation follows this method:

  • 若下一個成長小於目前記錄實體大小的 1/8,請建立 1 個能夠涵蓋成長大小的 VLF (從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始)If the next growth is less than 1/8 of current log physical size, then create 1 VLF that covers the growth size (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x))
  • 如果下一個成長大於目前記錄檔大小的 1/8,則使用 2014 版之前的方法:If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:
    • 若成長小於 64MB,請建立 4 個能夠涵蓋成長大小的 VLF (例如:成長若為 1 MB,請建立四個 256KB 的 VLF)If growth is less than 64MB, create 4 VLFs that cover the growth size (e.g. for 1 MB growth, create four 256KB VLFs)
    • 若成長介於 64MB 到 1GB 之間,請建立 8 個能夠涵蓋成長大小的 VLF (例如:成長若為 512MB,請建立八個 64MB 的 VLF)If growth is from 64MB up to 1GB, create 8 VLFs that cover the growth size (e.g. for 512MB growth, create eight 64MB VLFs)
    • 若成長大於 1GB,請建立 16 個能夠涵蓋成長大小的 VLF (例如:成長若為 8 GB,請建立十六個 512MB 的 VLF)If growth is larger than 1GB, create 16 VLFs that cover the growth size (e.g. for 8 GB growth, create sixteen 512MB VLFs)

如果記錄檔以許多少量增加而變得很龐大,將會產生許多虛擬記錄檔。If the log files grow to a large size in many small increments, they will have many virtual log files. 這樣會減慢資料庫啟動的速度,也會降低記錄備份和還原作業的執行速度。This can slow down database startup and also log backup and restore operations. 相反地,如果記錄檔設定為以少量次數或一次增加而變得很龐大,則會產生一些非常大的虛擬記錄檔。Conversely, if the log files are set to a large size with few or just one increment, they will have few very large virtual log files. 如需正確估計交易記錄檔的所需大小自動成長設定的詳細資訊,請參閱管理交易記錄檔的大小的<建議> 一節。For more information on properly estimating the required size and autogrow setting of a transaction log, refer to the Recommendations section of Manage the size of the transaction log file.

建議您使用達到最佳 VLF 分佈所需的增量,以接近最後所需大小的 size 值來指派記錄檔,並且也使用相對較大的 growth_increment 值。We recommend that you assign log files a size value close to the final size required, using the required increments to achieve optimal VLF distribution, and also have a relatively large growth_increment value. 若要判斷目前交易記錄大小的最佳 VLF 分佈,請參閱下方的提示。See the tip below to determine the optimal VLF distribution for the current transaction log size.

  • size 值,如 ALTER DATABASESIZE 引數所設定,是記錄檔的初始大小。The size value, as set by the SIZE argument of ALTER DATABASE is the initial size for the log file.
  • growth_increment 值 (也稱為自動成長值),如 ALTER DATABASEFILEGROWTH 引數所設定,是每次需要新空間時新增到檔案的空間量。The growth_increment value (also referred as the autogrow value), as set by the FILEGROWTH argument of ALTER DATABASE, is the amount of space added to the file every time new space is required.

如需 ALTER DATABASEFILEGROWTHSIZE 引數的詳細資訊,請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項For more information on FILEGROWTH and SIZE arguments of ALTER DATABASE, see ALTER DATABASE (Transact-SQL) File and Filegroup Options.

提示

若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈,以及達到所需大小的必要成長增量,請參閱此指令碼To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

交易記錄是循環使用的檔案。The transaction log is a wrap-around file. 例如,假設資料庫的一個實體記錄檔分成四個 VLF。For example, consider a database with one physical log file divided into four VLFs. 資料庫建立時,邏輯記錄檔從實體記錄檔的最前面開始。When the database is created, the logical log file begins at the start of the physical log file. 新的記錄會加在邏輯記錄檔的最後,並朝向實體記錄檔的結尾處擴充。New log records are added at the end of the logical log and expand toward the end of the physical log. 記錄截斷會釋出記錄出現在最小復原記錄序號 (MinLSN) 前面的所有虛擬記錄。Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). MinLSN 是成功回復全資料庫所需之最舊記錄檔記錄的記錄序號。The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. 範例資料庫中的交易記錄看起來如下圖所示。The transaction log in the example database would look similar to the one in the following illustration.

tranlog3

當邏輯記錄檔的結尾到達實體記錄檔的結尾時,新的記錄資料將寫回實體記錄檔的開頭處。When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.

tranlog4

只要邏輯記錄檔的結尾永遠不碰到邏輯記錄檔的開頭,此週期就會不斷地重複。This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log. 如果經常截斷舊的記錄,以便讓目前到下個檢查點之間建立的所有新記錄一定會有足夠的空間可以使用,記錄檔就永遠不會填滿。If the old log records are truncated frequently enough to always leave sufficient room for all the new log records created through the next checkpoint, the log never fills. 不過,如果邏輯記錄檔的結尾已到達邏輯記錄檔的開頭,會發生下列其中一種狀況:However, if the end of the logical log does reach the start of the logical log, one of two things occurs:

  • 如果記錄檔啟用 FILEGROWTH 設定,而且磁碟也有可用的空間,則檔案會以 growth_increment 參數所指定的數量擴大,並將新的記錄新增至延伸模組。If the FILEGROWTH setting is enabled for the log and space is available on the disk, the file is extended by the amount specified in the growth_increment parameter and the new log records are added to the extension. 如需 FILEGROWTH 設定的詳細資訊,請參閱 ALTER DATABASE 檔案及檔案群組選項 (Transact-SQL)For more information about the FILEGROWTH setting, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

  • 如果未啟用 FILEGROWTH 設定,或保存記錄檔的磁碟可用空間少於 growth_increment 所指定的數量,則會產生 9002 錯誤。If the FILEGROWTH setting is not enabled, or the disk that is holding the log file has less free space than the amount specified in growth_increment, an 9002 error is generated. 如需詳細資訊,請參閱為完整交易記錄進行疑難排解Refer to Troubleshoot a Full Transaction Log for more information.

如果記錄檔包含多個實體記錄檔,邏輯記錄檔會從頭到尾在所有的實體記錄檔移動之後,才繞回第一個實體記錄檔的起點。If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file.

重要

如需交易記錄檔管理的詳細資訊,請參閱管理交易記錄檔的大小For more information about transaction log size management, see Manage the Size of the Transaction Log File.

記錄截斷Log Truncation

為了避免記錄被填滿,必須截斷記錄。Log truncation is essential to keep the log from filling. 記錄截斷會從 SQL ServerSQL Server 資料庫的邏輯交易記錄中刪除非使用中的虛擬記錄檔,釋出邏輯記錄中的空間以供實體交易記錄重複使用。Log truncation deletes inactive virtual log files 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 were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. 不過,必須先進行檢查點作業,才能截斷記錄。However, before the log can be truncated, a checkpoint operation must occur. 檢查點會將目前記憶體中已修改的頁面 (稱為中途分頁) 和交易記錄資訊從記憶體寫入磁碟。A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. 執行檢查點時,交易記錄的非使用中部分會標示成可重複使用。When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. 之後,記錄截斷就可以釋出非使用中的部分。Thereafter, the inactive portion can be freed by log truncation. 如需檢查點的詳細資訊,請參閱資料庫檢查點 (SQL Server)For more information about checkpoints, see Database Checkpoints (SQL Server).

下圖將顯示截斷前後的交易記錄。The following illustrations show a transaction log before and after truncation. 第一張圖是顯示從未進行截斷的交易記錄。The first illustration shows a transaction log that has never been truncated. 目前,邏輯記錄正使用四個虛擬記錄檔。Currently, four virtual log files are in use by the logical log. 邏輯記錄檔是從第一個虛擬記錄檔的前面開始,並於虛擬記錄檔 4 結束。The logical log starts at the front of the first virtual log file and ends at virtual log 4. MinLSN 記錄位於虛擬記錄檔 3 中。The MinLSN record is in virtual log 3. 虛擬記錄檔 1 和虛擬記錄檔 2 僅包含非使用中的記錄檔記錄。Virtual log 1 and virtual log 2 contain only inactive log records. 這些記錄都可以截斷。These records can be truncated. 虛擬記錄 5 仍未使用而且不屬於目前邏輯記錄的一部分。Virtual log 5 is still unused and is not part of the current logical log.

tranlog2

第二張圖是顯示記錄截斷之後的內容。The second illustration shows how the log appears after being truncated. 虛擬記錄 1 和虛擬記錄 2 已經釋出以便重複使用。Virtual log 1 and virtual log 2 have been freed for reuse. 現在邏輯記錄檔是從虛擬記錄檔 3 的前面開始。The logical log now starts at the beginning of virtual log 3. 虛擬記錄檔 5 仍未使用,而且不屬於目前邏輯記錄檔的一部分。Virtual log 5 is still unused, and it is not part of the current logical log.

tranlog3

除了因為某種原因而延遲以外,記錄截斷會在發生下列事件之後自動進行:Log truncation occurs automatically after the following events, except when delayed for some reason:

  • 在簡單復原模式下,發生在檢查點之後。Under the simple recovery model, after a checkpoint.
  • 在完整復原模式或大量記錄復原模式下,上一次備份以來發生檢查點時的記錄備份之後。Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

記錄截斷可能會因為各種因素而延遲。Log truncation can be delayed by a variety of factors. 如果在記錄截斷中發生長時間的延遲,交易記錄可能會填滿。In the event of a long delay in log truncation, the transaction log can fill up. 如需詳細資訊,請參閱可能會延遲記錄截斷的因素針對完整交易記錄 (SQL Server 錯誤 9002) 進行疑難排解For information, see Factors that can delay log truncation and Troubleshoot a Full Transaction Log (SQL Server Error 9002).

預先寫入交易記錄Write-Ahead Transaction Log

本章節說明預先寫入交易記錄在將資料修改記錄至磁碟時所扮演的角色。This section describes the role of the write-ahead transaction log in recording data modifications to disk. SQL ServerSQL Server 會使用預先寫入記錄 (WAL) 演算法,而這項功能可確保在相關記錄檔的記錄寫入磁碟之前,不會將任何資料修改寫入磁碟。uses a write-ahead logging (WAL) algorithm, which guarantees that no data modifications are written to disk before the associated log record is written to disk. 如此可保留交易的 ACID 屬性。This maintains the ACID properties for a transaction.

若要了解預寫記錄檔的運作方式,您一定要知道如何將修改的資料寫入磁碟。To understand how the write-ahead log works, it is important for you to know how modified data is written to disk. SQL ServerSQL Server 會維護緩衝快取,當需要擷取資料時,就可以將資料頁讀取到其中。maintains a buffer cache into which it reads data pages when data must be retrieved. 當緩衝快取中的頁面被修改時,不會立即重新寫入磁碟;而是將頁面標示為「中途」 。When a page is modified in the buffer cache, it is not immediately written back to disk; instead, the page is marked as dirty. 在實際將資料頁寫入磁碟之前,可以進行多次邏輯寫入。A data page can have more than one logical write made before it is physically written to disk. 每次邏輯寫入時,都會有交易記錄插入至記載修改的記錄快取中。For each logical write, a transaction log record is inserted in the log cache that records the modification. 記錄檔記錄必須在關聯的中途分頁從緩衝區快取移除而寫入至磁碟之前,先寫入磁碟中。The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. 檢查點處理序會定期掃描緩衝快取,檢查是否有內含來自指定資料庫之頁面的緩衝區,並將所有中途分頁寫入磁碟。The checkpoint process periodically scans the buffer cache for buffers with pages from a specified database and writes all dirty pages to disk. 藉由建立一個點來確保所有中途分頁都已寫入磁碟中,檢查點可讓稍後的復原節省時間。Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

將緩衝區快取中之修改資料頁面寫入磁碟中的動作稱為清除頁面。Writing a modified data page from the buffer cache to disk is called flushing the page. SQL ServerSQL Server 含有防止中途分頁在寫入相關聯記錄檔記錄之前遭到清除的邏輯。has logic that prevents a dirty page from being flushed before the associated log record is written. 記錄檔記錄會在記錄檔緩衝區清除後寫入磁碟中。Log records are written to disk when the log buffers are flushed. 交易認可後或記錄檔緩衝區已滿時會發生此情況。This happens whenever a transaction commits or the log buffers become full.

交易記錄備份Transaction Log Backups

本章節提出有關如何備份和還原 (套用) 交易記錄的概念。This section presents concepts about how to back up and restore (apply) transaction logs. 在完整和大量記錄復原模式下,進行交易記錄 (「記錄備份」 ) 的例行備份,對復原資料而言是必要的。Under the full and bulk-logged recovery models, taking routine backups of transaction logs (log backups) is necessary for recovering data. 您可以在任何完整備份正在執行的同時備份記錄。You can back up the log while any full backup is running. 如需復原模型的詳細資訊,請參閱 SQL Server 資料庫的備份與還原For more information about recovery models, see Back Up and Restore of SQL Server Databases.

在建立第一個記錄備份之前,您必須建立完整備份,例如資料庫備份或檔案備份組中的第一個備份。Before you can create the first log backup, you must create a full backup, such as a database backup or the first in a set of file backups. 僅使用檔案備份來還原資料庫,可能會讓情況變得很複雜。Restoring a database by using only file backups can become complex. 因此,我們建議您盡可能先從完整資料庫備份開始。Therefore, we recommend that you start with a full database backup when you can. 之後,則需要定期備份交易記錄。Thereafter, backing up the transaction log regularly is necessary. 這不僅是要降低工作損失的風險,也是為了在必要時可以截斷交易記錄。This not only minimizes work-loss exposure but also enables truncation of the transaction log. 交易記錄通常在每個傳統記錄備份之後截斷。Typically, the transaction log is truncated after every conventional log backup.

重要

我們建議您經常進行充分的記錄備份以支援商務需求,特別是您對工作損失 (例如可能因損壞的記錄儲存體而引起) 的耐受性。We recommend taking frequent enough log backups to support your business requirements, specifically your tolerance for work loss such as might be caused by a damaged log storage. 進行記錄備份的頻率如何才適當,視您在工作損失風險的耐受性,與儲存、管理及可能還原記錄備份的容量之間所做的取捨而定。The appropriate frequency for taking log backups depends on your tolerance for work-loss exposure balanced by how many log backups you can store, manage, and, potentially, restore. 實作復原策略,以及特別是記錄備份頻率時,考慮使用必要的 RTORPOThink about the required RTO and RPO when implementing your recovery strategy, and specifically the log backup cadence. 每 15 到 30 分鐘進行一次記錄備份可能就足夠了。Taking a log backup every 15 to 30 minutes might be enough. 如果您的業務需要將工作損失風險減至最低,請考慮更頻繁地進行記錄備份。If your business requires that you minimize work-loss exposure, consider taking log backups more frequently. 較頻繁的記錄備份還會帶來另一優點,就是增加記錄截斷的頻率,從而產生較小的記錄檔。More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files.

重要

若要限制您需要還原的記錄備份數目,定期備份資料是基本作業。To limit the number of log backups that you need to restore, it is essential to routinely back up your data. 例如,您可能會排程每週的完整資料庫備份和每日的差異資料庫備份。For example, you might schedule a weekly full database backup and daily differential database backups.
同樣地,實作復原策略,以及特別是完整和差異資料庫備份頻率時,考慮使用必要的 RTORPOAgain, think about the required RTO and RPO when implementing your recovery strategy, and specifically the full and differential database backup cadence.

如需交易記錄備份的詳細資訊,請參閱交易記錄備份 (SQL Server)For more information about transaction log backups, see Transaction Log Backups (SQL Server).

記錄鏈結The Log Chain

連續的記錄備份順序稱為「記錄檔鏈結」 。A continuous sequence of log backups is called a log chain. 記錄鏈結以資料庫的完整備份開始。A log chain starts with a full backup of the database. 通常,只有在首次備份資料庫,或將簡單復原模式切換為完整或大量記錄復原模式之後,才會開始新的記錄鏈結。Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery. 除非您在建立完整資料庫備份時選擇覆寫現有的備份組,否則現有的記錄鏈結會維持不變。Unless you choose to overwrite existing backup sets when creating a full database backup, the existing log chain remains intact. 透過維持不變的記錄鏈結,您可以從媒體集中的任何完整資料庫備份還原資料庫,後面接著所有後續的記錄備份,直到復原點為止。With the log chain intact, you can restore your database from any full database backup in the media set, followed by all subsequent log backups up through your recovery point. 復原點可能是上一個記錄備份的結尾,或是任何記錄備份中的特定復原點。The recovery point could be the end of the last log backup or a specific recovery point in any of the log backups. 如需詳細資訊,請參閱套用交易記錄備份 (SQL Server)For more information, see Transaction Log Backups (SQL Server).

若要將資料庫還原到失敗點,必須有完整的記錄鏈結。To restore a database up to the point of failure, the log chain must be intact. 也就是說,必須將交易記錄備份的順序不間斷地延伸到失敗點。That is, an unbroken sequence of transaction log backups must extend up to the point of failure. 這個記錄順序必須從何處開始視您要還原的資料備份類型而定:資料庫、部分或檔案。Where this sequence of log must start depends on the type of data backups you are restoring: database, partial, or file. 如果是資料庫或部分備份,記錄備份的順序必須從資料庫或部分備份的結尾延伸。For a database or partial backup, the sequence of log backups must extend from the end of a database or partial backup. 如果是檔案備份組,記錄備份的順序則必須從完整檔案備份組的起始來延伸。For a set of file backups, the sequence of log backups must extend from the start of a full set of file backups. 如需詳細資訊,請參閱套用交易記錄備份 (SQL Server)For more information, see Apply Transaction Log Backups (SQL Server).

還原記錄備份Restore Log Backups

還原記錄備份會向前復原交易記錄中所記錄的變更,以重新建立開始進行記錄備份作業時的正確資料庫狀態。Restoring a log backup rolls forward the changes that were recorded in the transaction log to re-create the exact state of the database at the time the log backup operation started. 還原資料庫時,您必須還原在所要的完整資料庫備份之後建立的記錄備份,或者必須從您所還原之第一個檔案備份的起始開始還原記錄備份。When you restore a database, you will have to restore the log backups that were created after the full database backup that you restore, or from the start of the first file backup that you restore. 一般而言,還原最近的資料或差異備份之後,您必須還原一連串的記錄備份,直到復原點為止。Typically, after you restore the most recent data or differential backup, you must restore a series of log backups until you reach your recovery point. 然後再復原資料庫。Then, you recover the database. 這樣會回復所有在復原啟動時未完成的交易,並使資料庫回到線上。This rolls back all transactions that were incomplete when the recovery started and brings the database online. 復原資料庫之後,您不能再還原其他備份。After the database has been recovered, you cannot restore any more backups. 如需詳細資訊,請參閱套用交易記錄備份 (SQL Server)For more information, see Apply Transaction Log Backups (SQL Server).

檢查點與記錄檔的使用中部份Checkpoints and the Active Portion of the Log

檢查點可從目前資料庫的緩衝區快取將中途 (Dirty) 資料頁排清至磁碟。Checkpoints flush dirty data pages from the buffer cache of the current database to disk. 這可將資料庫完整復原必須處理的記錄部分減至最少。This minimizes the active portion of the log that must be processed during a full recovery of a database. 在完整復原時,將執行下列動作類型:During a full recovery, the following types of actions are performed:

  • 在系統停止之前,尚未排清至磁碟的修改記錄會向前復原。The log records of modifications not flushed to disk before the system stopped are rolled forward.
  • 未完成之交易 (例如,未包含 COMMIT 或 ROLLBACK 記錄的交易) 所關聯的所有修改都必須回復。All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.

檢查點作業Checkpoint Operation

檢查點將在資料庫中執行下列處理序:A checkpoint performs the following processes in the database:

  • 將記錄寫入記錄檔,並標示檢查點的起點。Writes a record to the log file, marking the start of the checkpoint.

  • 儲存記錄於檢查點記錄鏈結中的檢查點資訊。Stores information recorded for the checkpoint in a chain of checkpoint log records.

    記錄於檢查點的某項資訊是必須出現於成功回復全資料庫中之第一筆記錄檔記錄的記錄序號 (LSN)。One piece of information recorded in the checkpoint is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. 此 LSN 就稱為「最小復原 LSN」(MinLSN)。This LSN is called the Minimum Recovery LSN (MinLSN). MinLSN 是下列項目的最小值:The MinLSN is the minimum of the:

    • 檢查點起點的 LSN。LSN of the start of the checkpoint.
    • 最舊使用中交易之起點的 LSN。LSN of the start of the oldest active transaction.
    • 尚未傳遞至散發資料庫之最舊複寫交易起點的 LSN。LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.

    檢查點的記錄還包含已經修改資料庫之所有使用中交易的清單。The checkpoint records also contain a list of all the active transactions that have modified the database.

  • 如果資料庫使用簡單復原模式,就會將 MinLSN 前面的空間標示為可重複使用。If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN.

  • 將所有中途 (Dirty) 記錄與資料頁寫入磁碟之中。Writes all dirty log and data pages to disk.

  • 將標示檢查點終點的記錄寫入記錄檔中。Writes a record marking the end of the checkpoint to the log file.

  • 將此鏈結起點的 LSN 寫入資料庫開機頁面中。Writes the LSN of the start of this chain to the database boot page.

產生檢查點的活動Activities that cause a Checkpoint

在下列情況下會產生檢查點:Checkpoints occur in the following situations:

  • 明確執行 CHECKPOINT 陳述式。A CHECKPOINT statement is explicitly executed. 在連接的目前資料庫中發生檢查點。A checkpoint occurs in the current database for the connection.
  • 在資料庫中執行最基本的登入作業;例如,在使用大量記錄復原模式的資料庫中執行大量復製作業。A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.
  • 使用 ALTER DATABASE 加入或移除資料庫檔案。Database files have been added or removed by using ALTER DATABASE.
  • 以 SHUTDOWN 陳述式或透過停止 SQL Server (MSSQLSERVER) 服務來停止 SQL Server 執行個體。An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. 任何一項動作都會導致在 SQL Server 執行個體的每個資料庫中產生檢查點。Either action causes a checkpoint in each database in the instance of SQL Server.
  • SQL Server 執行個體會定期在每個資料庫中產生自動檢查點,以減少執行個體需要復原資料庫的時間。An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.
  • 取得資料庫備份。A database backup is taken.
  • 執行需要關閉資料庫的活動。An activity requiring a database shutdown is performed. 例如,AUTO_CLOSE 為 ON,且上次使用者與資料庫的連接已經關閉,或是已變更資料庫選項,因此需要重新啟動資料庫。For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

自動檢查點Automatic Checkpoints

SQL Server Database Engine 會產生自動檢查點。The SQL Server Database Engine generates automatic checkpoints. 自動檢查點之間的間隔,是根據所使用的記錄空間量以及自上個檢查點後所經過的時間而設置。The interval between automatic checkpoints is based on the amount of log space used and the time elapsed since the last checkpoint. 如果資料庫只做了一些修改,自動檢查點之間的時間間隔可能會有很多變化且很長。The time interval between automatic checkpoints can be highly variable and long, if few modifications are made in the database. 如果修改了許多資料,自動檢查點就會經常發生。Automatic checkpoints can also occur frequently if lots of data is modified.

請使用 [復原間隔] 伺服器組態選項,針對伺服器執行個體上的所有資料庫,計算自動檢查點之間的間隔。Use the recovery interval server configuration option to calculate the interval between automatic checkpoints for all the databases on a server instance. 此選項指定了 Database Engine 在系統重新啟動的過程中,用來復原資料庫的時間上限。This option specifies the maximum time the Database Engine should use to recover a database during a system restart. 將估計它在復原作業的 [復原間隔] 中可處理多少記錄。The Database Engine estimates how many log records it can process in the recovery interval during a recovery operation.

自動檢查點之間的間隔也是取決於復原模式:The interval between automatic checkpoints also depends on the recovery model:

  • 若資料庫使用完整復原模式或大量記錄復原模式,每次記錄達到 Database Engine 可在「復原間隔」選項指定之時間內處理的數目時,就會產生自動檢查點。If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

  • 若資料庫使用的是簡單復原模式,每次當記錄的數目到達下列兩個數值的較小者時,就會產生一個自動檢查點:If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

    • 記錄填滿百分之 70 時。The log becomes 70 percent full.
    • 記錄的數目到達 Database Engine 估計可在「復原間隔」選項指定之時間內處理的數目時。The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

如需設定復原間隔的詳細資訊,請參閱 設定復原間隔伺服器組態選項For information about setting the recovery interval, see Configure the recovery interval Server Configuration Option.

提示

資料庫管理員可使用 -k SQL Server 進階安裝選項,根據某些類型的檢查點之 I/O 子系統輸送量,來調節檢查點 I/O 行為。The -k SQL Server advanced setup option enables a database administrator to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. -k 安裝選項會套用到自動檢查點以及任何未調節的檢查點。The -k setup option applies to automatic checkpoints and any otherwise unthrottled checkpoints.

若資料庫使用的是簡單復原模式,自動檢查點將截斷交易記錄的未使用部分。Automatic checkpoints truncate the unused section of the transaction log if the database is using the simple recovery model. 然而,如果資料庫使用的是完整或大量記錄復原模式,自動檢查點將不會截斷記錄。However, if the database is using the full or bulk-logged recovery models, the log is not truncated by automatic checkpoints. 如需詳細資訊,請參閱 交易記錄For more information, see The Transaction Log.

CHECKPOINT 陳述式現在提供一個選擇性的 checkpoint_duration 引數,指定完成檢查點之要求的時間週期 (以秒為單位)。The CHECKPOINT statement now provides an optional checkpoint_duration argument that specifies the requested period of time, in seconds, for checkpoints to finish. 如需詳細資訊,請參閱 CHECKPOINTFor more information, see CHECKPOINT.

使用中的記錄Active Log

從 MinLSN 到最後寫入之記錄的記錄檔部分,稱為記錄的使用中部分,或是「使用中的記錄」。The section of the log file from the MinLSN to the last-written log record is called the active portion of the log, or the active log. 這是需要進行完整資料庫復原的記錄部分。This is the section of the log required to do a full recovery of the database. 沒有任何使用中的記錄部分可被截斷。No part of the active log can ever be truncated. 所有的記錄截斷動作必須發生於 MinLSN 之前的記錄部分。All log records must be truncated from the parts of the log before the MinLSN.

下圖將顯示包含兩個使用中交易之交易記錄檔結尾的簡化版本。The following illustration shows a simplified version of the end-of-a-transaction log with two active transactions. 檢查點記錄已被壓縮成一個記錄。Checkpoint records have been compacted to a single record.

active_log

LSN 148 是交易記錄中最後一個記錄。LSN 148 is the last record in the transaction log. 當記錄於 LSN 147 的檢查點經過處理之後,將會認可 Tran 1,而 Tran 2 將成為唯一的使用中交易。At the time that the recorded checkpoint at LSN 147 was processed, Tran 1 had been committed and Tran 2 was the only active transaction. 這會使得 Tran 2 的第一個記錄成為最後檢查點之使用中交易的最早記錄。That makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. 這可讓 LSN 142,也就是 Tran 2 的 Begin 交易記錄,成為 MinLSN。This makes LSN 142, the Begin transaction record for Tran 2, the MinLSN.

長時間執行的交易Long-Running Transactions

使用中的記錄必須包含所有未認可交易的每個部分。The active log must include every part of all uncommitted transactions. 啟動交易、但尚未認可或復原交易的應用程式,將不會讓 Database Engine 將 MinLSN 往前移。An application that starts a transaction and does not commit it or roll it back prevents the Database Engine from advancing the MinLSN. 這可能導致兩類問題:This can cause two types of problems:

  • 若系統是在交易已執行許多未認可的修改之後關機,接著重新啟動之復原階段所需的時間可能比 [復原間隔] 選項所指定的時間多很多。If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take much longer than the time specified in the recovery interval option.
  • 因為記錄無法截斷超過 MinLSN,記錄可能會成長得很大。The log might grow very large, because the log cannot be truncated past the MinLSN. 即使資料庫使用的是簡單的復原模式,此狀況也會發生,因為交易記錄檔通常會在每個自動檢查點截斷。This occurs even if the database is using the simple recovery model, in which the transaction log is generally truncated on each automatic checkpoint.

複寫交易Replication Transactions

「記錄讀取器代理程式」會監視針對異動複寫設定之每個資料庫的交易記錄,並將標示要複寫的交易從交易記錄複製到散發資料庫中。The Log Reader Agent monitors the transaction log of each database configured for transactional replication, and it copies the transactions marked for replication from the transaction log into the distribution database. 使用中的記錄必須包含所有標示要複寫但尚未傳遞到散發資料庫的交易。The active log must contain all transactions that are marked for replication, but that have not yet been delivered to the distribution database. 如果這些交易未及時複寫的話,將使記錄無法截斷。If these transactions are not replicated in a timely manner, they can prevent the truncation of the log. 如需詳細資訊,請參閱 異動複寫For more information, see Transactional Replication.

另請參閱See also

建議您閱讀下列文章和書籍,了解交易記錄和記錄管理最佳做法的其他相關資訊。We recommend the following articles and books for additional information about the transaction log and log management best practices.

交易記錄 (SQL Server) The Transaction Log (SQL Server)
管理交易記錄檔的大小 Manage the size of the transaction log file
交易記錄備份 (SQL Server) Transaction Log Backups (SQL Server)
資料庫檢查點 (SQL Server) Database Checkpoints (SQL Server)
設定 recovery interval 伺服器設定選項 Configure the recovery interval Server Configuration Option
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)
了解 SQL Server 中的記錄與復原,作者 Paul Randal Understanding Logging and Recovery in SQL Server by Paul Randal
《SQL Server Transaction Log Management》,作者 Tony Davis 和 Gail ShawSQL Server Transaction Log Management by Tony Davis and Gail Shaw