延迟的事务 (SQL Server)Deferred Transactions (SQL Server)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

SQL ServerSQL Server 企业版中,如果在数据库启动过程中回滚(撤消)所需的数据处于脱机状态,则损坏的事务可能延迟。In SQL ServerSQL Server Enterprise, a corrupted transaction can become deferred if data required by rollback (undo) is offline during database startup. “延迟的事务” 是指前滚阶段结束时未提交的事务或遇到错误而无法回滚的事务。A deferred transaction is a transaction that is uncommitted when the roll forward phase finishes and that has encountered an error that prevents it from being rolled back. 因为无法回滚事务,所以事务将延迟。Because the transaction cannot be rolled back, it is deferred.


仅在 SQL ServerSQL Server Enterprise 中才会延迟损坏的事务。Corrupted transactions are deferred only in SQL ServerSQL Server Enterprise. 在其他版本的 SQL ServerSQL Server中,事务损坏将导致启动失败。In other editions of SQL ServerSQL Server, a corrupted transaction causes startup to fail.

一般来说,如果前滚数据库时遇到 I/O 错误而无法读取事务所需的页面,则将导致事务延迟。Generally, a deferred transaction occurs because, while the database was being rolled forward, an I/O error prevented reading a page that was required by the transaction. 但是,文件级的错误也会导致延迟的事务。However, an error at the file level can also cause deferred transactions. 如果部分还原顺序在某点停止,在该点上必须对事务进行回滚而事务所需的数据处于脱机状态,这亦会导致事务延迟。A deferred transaction can also occur when a partial restore sequence stops at a point at which transaction rollback is necessary and a transaction requires data that is offline.

如果用户事务在回滚过程中遇到 I/O 错误,则会导致整个数据库脱机。User transactions that are rolling back and hit an I/O error cause the whole database to go offline. 当数据库恢复联机时,重做操作将重新获得它曾持有的所有锁,并尝试回滚所有未提交的事务。When the database is brought back online, the redo reacquires all the locks it had and tries to roll back all the uncommitted transactions. 事务所修改的所有数据都将保持适当锁定的状态,直到可以回滚事务时为止。All data modified by a transaction remains appropriately locked until the transaction can roll back. 在修复损坏并重新启动数据库时,或通过联机还原在数据库保持联机的同时解决了延迟的事务时,无法回滚的事务将放弃其持有的锁。Transactions that cannot be rolled back will give up their locks when the corruption is fixed and the database restarted or, after an online restore, when the deferred transactions are resolved while the database remains online. 在此之前,延迟的事务可持有锁,以防止对整个数据库执行某些操作。Until that point, a deferred transaction can hold locks that prevent certain operations on the database as a whole. 例如,如果延迟的事务包含 CREATE TABLE 指令,则在解决延迟的事务之前,任何用户都无法创建表。For example, if a deferred transaction contains a CREATE TABLE instruction, no user can create a table until the deferred transaction has been resolved.

如果段落还原将数据库恢复到某个点,在该点上有一个或多个活动事务会影响尚未还原并处于脱机状态的文件组,则将发生事务延迟。Deferred transaction can also occur because a piecemeal restore recovers a database to a point at which one or more active transactions are affecting a filegroup that has not yet been restored and is offline. 因为无法回滚事务,所以事务将延迟。Because the transactions cannot be rolled back, they become deferred.

下表列出了导致数据库执行恢复的操作以及发生 I/O 问题的后果。The following table lists the actions that cause a database to perform recovery and the outcome if an I/O problem occurs.

操作Action 解决方法(如果遇到 I/O 问题或所需数据处于脱机状态)Resolution (if I/O problems occur or required data is offline)
服务器启动Server start 延迟的事务Deferred transaction
还原Restore 延迟的事务Deferred transaction
附加Attach 附加失败Attach fails
自动重新启动Autorestart 延迟的事务Deferred transaction
创建数据库或数据库快照Create database or database snapshot 创建失败Creation fails
在数据库镜像上执行的重做操作Redo on database mirroring 延迟的事务Deferred transaction
文件组脱机Filegroup is offline 延迟的事务Deferred transaction

要求和限制Requirements and Limitations

  • 数据库必须使用完整恢复模式或大容量日志恢复模式。The database must use the FULL or BULK-LOGGED recovery model.
  • 必须为数据库完成至少一个数据库和日志备份At least one database and log backup must have been completed for the database
  • 延迟的事务不适用于数据库联机后回滚事务期间遇到的错误。Deffered transactions do not apply to errors encountered during a rollback of a transaction after the database is online. (例如运行时错误)(e.g. a runtime error)
  • 数据库附加期间,无法为恢复失败延迟事务Transactions cannot be deferred for recovery failures during a database attach
  • 诸如系统事务Some transactions such as system transactions (Ex. (例如页面分配)之类的某些事务无法延迟page allocation) cannot be deferred

将事务移出 DEFERRED 状态Moving a Transaction Out of the DEFERRED State


延迟的事务会使事务日志保持活动状态。Deferred transactions keep the transaction log active. 在延迟的事务脱离延迟状态之前无法截断包含这些事务的虚拟日志文件。A virtual log file that contains any deferred transactions cannot be truncated until those transactions are moved out of the deferred state. 有关日志截断的详细信息,请参阅事务日志 (SQL Server)For more information about log truncation, see The Transaction Log (SQL Server).

若要使事务脱离延迟状态,数据库必须在没有任何 I/O 错误的情况下顺利启动。To move the transaction out of the deferred state, the database must start cleanly without any I/O errors. 如果存在延迟的事务,则必须修复 I/O 错误源。If deferred transactions exist, you must fix the source of the I/O errors. 下面便是可用的解决方案,它们按照通常尝试执行的顺序列出:The available solutions, listed in the order in which they are typically tried, are as follows:

  • 重新启动数据库。Restart the database. 如果问题是暂时的,数据库应该会启动,而且没有延迟的事务。If the problem was transient, the database should start without deferred transactions.

  • 如果事务由于文件组脱机而延迟,请将文件组重新联机。If the transactions were deferred because a filegroup was offline, bring the filegroup back online.

    若要使脱机文件组恢复联机,请使用以下 Transact-SQLTransact-SQL 语句:To bring an offline filegroup back online, use the following Transact-SQLTransact-SQL statement:

    RESTORE DATABASE database_name FILEGROUP=<filegroup_name>  
  • 还原数据库。Restore the database. 进行联机还原后,将解决所有延迟的事务。After an online restore, any deferred transactions are resolved.

    在完整恢复模式或大容量日志恢复模式下,如果延迟的事务仅仅是由少量的损坏页引起的,则可以通过联机页面还原解决这些错误(如果支持)。Under the full or bulk-logged recovery model, if the deferred transactions were caused by only a few corrupted pages, an online page restore might resolve the errors (where supported).

  • 如果不再需要因脱机状态而导致事务延迟的文件组,请使这样的脱机文件组失效。If you are no longer require a filegroup whose offline status is causing deferred transactions, make the offline filegroup defunct. 这样的文件组失效之后,由于这些文件组的脱机而延迟的事务将脱离延迟状态。Transactions that were deferred because the filegroup was offline are moved out of the deferred state after the filegroup becomes defunct.


    从不恢复失效的文件组。A defunct filegroup can never be recovered.

    有关详细信息,请参阅 删除失效文件组 (SQL Server)For more information, see Remove Defunct Filegroups (SQL Server).

  • 如果由于页的错误而致使事务延迟,并且没有数据库的完好备份,请按照以下步骤修复数据库:If transactions were deferred because of a bad page and if a good backup of the database does not exist, use the following process to repair the database:

    • 首先通过执行以下 Transact-SQLTransact-SQL 语句将数据库置为紧急模式:First put the database into emergency mode by executing the following Transact-SQLTransact-SQL statement:

      ALTER DATABASE <database_name> SET EMERGENCY  

      有关紧急模式的信息,请参阅 Database StatesFor information about emergency mode, see Database States.

    • 然后,通过在以下 DBCC 语句之一中使用 DBCC REPAIR_ALLOW_DATA_LOSS 选项修复数据库:DBCC CHECKDBDBCC CHECKALLOCDBCC CHECKTABLEThen, repair the database by using the DBCC REPAIR_ALLOW_DATA_LOSS option in one of the following DBCC statements: DBCC CHECKDB, DBCC CHECKALLOC, or DBCC CHECKTABLE.

      在遇到错误的页时,DBCC 将释放该页并修复所有相关错误。When DBCC encounters the bad page, DBCC deallocates it and repairs any related errors. 此方法可以使数据库重新联机并处于物理上一致的状态。This approach enables the database to be brought back online in a physically consistent state. 但是,还可能会丢失其他数据;因此,应在不得已的情况下才使用此方法。However, additional data might also be lost; therefore, this approach should be used as a last resort.

另请参阅See Also

还原和恢复概述 (SQL Server) Restore and Recovery Overview (SQL Server)
删除失效文件组 (SQL Server) Remove Defunct Filegroups (SQL Server)
文件还原(完整恢复模式) File Restores (Full Recovery Model)
文件还原(简单恢复模式) File Restores (Simple Recovery Model)
还原页 (SQL Server) Restore Pages (SQL Server)
段落还原 (SQL Server) Piecemeal Restores (SQL Server)
RESTORE (Transact-SQL)RESTORE (Transact-SQL)