SQL Server 事务日志体系结构和管理指南SQL Server Transaction Log Architecture and Management Guide

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) 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. 日志记录按创建的顺序存储:如果 LSN2 大于 LSN1,则 LSN2 所标识的日志记录描述的更改发生在日志记录 LSN1 描述的更改之后。Log records are stored in a serial sequence as they are created such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN1. 每条日志记录都包含其所属事务的 ID。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, active log, or tail of the 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). 有关事务日志支持的操作的详细信息,请参阅事务日志 (SQL Server)For more information on operations supported by the transaction log, see The Transaction Log (SQL Server).

差异和日志备份将还原的数据库推到稍后的时间,该时间与一个更高的 LSN 相对应。Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.

事务日志物理体系结构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 数据库引擎SQL Server Database Engine 在内部将每物理日志文件分成多个虚拟日志文件 (VLF)。The SQL Server 数据库引擎SQL 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 Engine 在创建或扩展日志文件时动态选择虚拟日志文件的大小。The 数据库引擎Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. 数据库引擎Database Engine 尝试维护少量的虚拟文件。The 数据库引擎Database 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,则使用 pre-2014 方法:If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:
    • 如果增长少于 64 MB,创建 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)
    • 如果增长在 64 MB 到 1GB 之间,创建 8 个 VLF,补偿此增长大小(如增长 512 MB,创建八个 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.

建议为日志文件分配一个接近于最终所需大小的 size 值,使用所需增量实现最佳 VLF 分发,并且还要分配一个相对较大的 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.

  • ALTER DATABASESIZE 参数设置的 size 值是指日志文件的初始大小 。The size value, as set by the SIZE argument of ALTER DATABASE is the initial size for the log file.
  • ALTER DATABASEFILEGROWTH 参数设置的 growth_increment 值(也称为自动增长值)是指每次需要新空间时添加到文件的空间大小。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. CHECKPOINT 可创建一个检查点,在该点保证全部脏页都已写入磁盘,从而在以后的恢复过程中节省时间。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. 实现恢复策略时,请考虑必需的 RTORPO,特别是日志备份频率。Think 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.
同样,实现恢复策略时,请考虑所需 RTORPO,尤其是完整和差异的数据库备份频率。Again, 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

检查点将脏数据页从当前数据库的缓冲区高速缓存刷新到磁盘上。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),该 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.

  • 将所有脏日志和数据页写入磁盘。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 数据库引擎生成自动检查点。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. 此选项指定数据库引擎在系统重新启动时恢复数据库所用的最长时间。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:

  • 如果数据库使用的是完整恢复模式或批量日志恢复模式,则每当日志记录数达到数据库引擎估计在“恢复间隔”选项中指定的时间内可以处理的数量时,便会生成一个自动检查点。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.
    • 日志记录数达到数据库引擎估计在“恢复间隔”选项指定的时间内能够处理的记录数。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 的开始事务记录)成为 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.

SQL Server 2019 (15.x)SQL Server 2019 (15.x)Azure SQL 数据库Azure SQL Database 开始,可以使用加速数据库恢复来避免恢复长时间运行的事务和上述问题。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) and in Azure SQL 数据库Azure SQL Database, recovery of long-running transactions and the problems described above can be avoided by using Accelerated database recovery.

复制事务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)
配置恢复间隔服务器配置选项 Configure the recovery interval Server Configuration Option
加速数据库恢复 Accelerated database recovery
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 事务日志管理(作者:Tony Davis 和 Gail Shaw)SQL Server Transaction Log Management by Tony Davis and Gail Shaw