事务日志 (SQL Server)The Transaction Log (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否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 Engine 检测到错误(例如失去与客户端的通信),就使用日志记录回滚未完成的事务所做的修改。If an application issues a ROLLBACK statement, or if the 数据库引擎Database 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 Engine 重新应用日志中记录的所有修改,以前滚所有事务。As you restore each log backup, the 数据库引擎Database Engine reapplies all the modifications recorded in the log to roll forward all the transactions. 当最后的日志备份还原后,数据库引擎Database Engine 将使用日志信息回滚到该点未完成的所有事务。When the last log backup is restored, the 数据库引擎Database 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. 有关详细信息,请参阅 AlwaysOn 故障转移群集实例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 数据库引擎SQL Server Database Engine 事务日志的特征:Characteristics of the SQL Server 数据库引擎SQL Server Database Engine transaction log:

  • 事务日志是作为数据库中的单独的文件或一组文件实现的。The transaction log is implemented as a separate file or set of files in the database. 日志缓存与数据页的缓冲区高速缓存是分开管理的,因此可在SQL Server 数据库引擎SQL 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 数据库引擎SQL 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.

重要

有关如何响应已满事务日志的信息,请参阅解决事务日志已满的问题(SQL Server 错误 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 描述Description
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 ReplicationFor 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)

有关间接检查点的信息,请参阅数据库检查点 (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)