MSSQLSERVER_3414MSSQLSERVER_3414

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

详细信息Details

AttributeAttribute Value
产品名称Product Name SQL ServerSQL Server
事件 IDEvent ID 34143414
事件源Event Source MSSQLSERVERMSSQLSERVER
组件Component SQLEngineSQLEngine
符号名称Symbolic Name REC_GIVEUPREC_GIVEUP
消息正文Message Text 恢复期间出错,导致数据库 '%.*ls' (数据库 ID %d)无法重新启动。An error occurred during recovery, preventing the database '%.*ls' (database ID %d) from restarting. 请诊断并纠正这些恢复错误,或者从已知的正确备份中还原。Diagnose the recovery errors and fix them, or restore from a known good backup. 如果无法更正错误,或者为意外错误,请与技术支持人员联系。If errors are not corrected or expected, contact Technical Support.

说明Explanation

已恢复指定的数据库,但无法启动它,因为在恢复期间出现了错误。The specified database was recovered, but failed to start, because errors occurred during recovery. 此错误使数据库进入 SUSPECT 状态。This error has put the database in the SUSPECT state. 主文件组以及可能其他文件组可疑并可能受损。The primary filegroup, and possibly other filegroups, are suspect and may be damaged. SQL ServerSQL Server 启动过程中无法恢复数据库,因此无法使用该数据库。The database cannot be recovered during startup of SQL ServerSQL Server and is therefore unavailable. 需要用户执行操作来解决问题。User action is required to resolve the problem. 在 SQL Server Management Studio(数据库图标旁边)中以及查看 sys.databases.state_desc 列时,你将看到 SUSPECT 状态。You will see the SUSPECT status in both SQL Server Management Studio (next to the database icon) and when you look at the sys.databases.state_desc column. 在此状态下尝试使用数据库会导致以下错误:Any attempt to use a database in this state will result in the following error:

Msg 926, Level 14, State 1, Line 1 
Database 'mydb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information

请注意,tempdb 中发生此错误时,SQL ServerSQL Server 实例将关闭。Note that when this error occurs in tempdb, the SQL ServerSQL Server instance shuts down.

原因Cause

此错误可能是由在某次尝试启动服务器实例或恢复数据库的过程中系统上存在的暂时性条件导致的。This error can be caused by a transient condition that existed on the system during a given attempt to start up the server instance or to recover a database. 此错误也可能是由当您每次尝试启动数据库时发生的永久性错误导致的。This error can also be caused by a permanent failure that occurs every time that you attempt to start the database. 通常,可以在错误日志或事件日志中错误 3414 之前的错误中找到恢复失败的原因。The cause of the recovery failure is typically found in error(s) that precedes Error 3414 in the ERRORLOG or Event Log. 日志文件中之前的错误包含相同的 spid 值。The preceding error in the log file contains the same spid value. 例如,以下恢复失败的原因是尝试读取日志块时发生校验和错误。For example, the following recovery failure is due to a checksum error when trying to read a log block. 请注意,spid15s 存在于所有行中:Note spid15s is present in all lines:

2020-03-31 17:33:13.00 spid15s     Error: 824, Severity: 24, State: 4.  
2020-03-31 17:33:13.00 spid15s     SQL Server detected a logical consistency-based I/O error: (bad checksum). It occurred during a read of page (0:-1) in database ID 13 at offset 0x0000000000b800 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\mydb_log.LDF'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.   
2020-03-31 17:33:13.16 spid15s     Error: 3414, Severity: 21, State: 1.  
2020-03-31 17:33:13.16 spid15s     An error occurred during recovery, preventing the database 'mydb' (database ID 13) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support

各种错误都可能导致数据库恢复失败。There are a wide range of errors that could cause database recovery to fail. 尽管必须逐一评估每个错误,但解决数据库恢复失败的方法通常如下文“用户操作”部分中所述。While you must evaluate each error on a case by case basis, the resolution to a database recovery failure is typically the same as described in the User Action section below.

用户操作User action

有关错误 3414 出现原因的信息,请检查 Windows 事件日志或错误日志以了解有关指示特定故障的先前错误。For information about the cause of this occurrence of error 3414, examine the Windows Event Log or ERRORLOG for a previous error that indicates the specific failure. 相应的用户操作取决于 Windows 事件日志中的信息是否指示该 SQL ServerSQL Server 错误由暂时条件或永久性故障导致。The appropriate user action depends on whether the information in the Windows Event Log indicates that the SQL ServerSQL Server error was caused by a transient condition or a permanent failure. 错误消息指示“诊断并纠正这些恢复错误,或者从已知的正确备份中还原”。The error message states to "diagnose recovery errors and fix them, or restore from a known good backup". 因此,你可以尝试纠正遇到的错误以完成恢复(请参阅可纠正的错误和延迟的事务)。Therefore, you can attempt to correct the error that you encounter to allow recovery to complete (see Correctable errors and deferred transactions).

如果无法纠正错误,则解决此问题的最优先选择是从完好的备份中还原。If the errors cannot be corrected, the first and best option to resolve this problem is to restore from a good backup. 如果无法从备份中恢复,你还有两种其他方法可以选择,但它们无法保证完整地恢复数据:将紧急修复与 DBCC CHECKDB 结合使用,或尝试将尽可能多的数据复制到另一个数据库。However, if you cannot recover from a backup, you have two additional options, which do not guarantee full data recovery: use emergency repair with DBCC CHECKDB or attempt to copy out as much data as possible to another database.

  1. 从上一个已知完好的数据库备份中还原Restore from the last known good database backup
  2. 使用 DBCC CHECKDB 提供的紧急修复方法Use the emergency repair method provided by DBCC CHECKDB
  3. 尝试将尽可能多的数据复制到另一个数据库。Attempt to copy out as much data as possible to another database.

第一种方法是还原完好的数据库备份,这是使数据库进入已知一致状态的最佳选择。The first method of restoring a good database backup is the best choice to bring a database to a known consistent state.

如果备份不可用,次优选择是使数据库处于联机且可访问的状态。The second best choice, if no backup is available, is to get the database online and accessible. 但必须意识到,由于恢复失败,无法保证事务性一致性。However, you must realize that transactional consistency cannot be guaranteed since recovery failed. 你无法得知哪些事务应该回滚或前滚,但由于恢复失败而未回滚或前滚。There is no way to know what transactions should have been rolled back or rolled forward but were not allowed because of the recovery failure. 有关紧急修复的步骤,请参阅 DBCC CHECKDB 文档中的在紧急模式下解决数据库错误部分。The steps to proceed with emergency repair are described in the section titled Resolving Database Errors in Emergency Mode in the DBCC CHECKDB documentation.

如果紧急修复不起作用,并且你想尝试将某些数据抢救到另一个数据库,则可以通过 ALTER DATABASE SET EMERGENCY 命令将数据库设置为紧急模式来访问数据库。If emergency repair does not work and you want to try to salvage some data to another database, the way to get access to the database is by setting the database in emergency mode via the ALTER DATABASE SET EMERGENCY command. 然后,你便可以尝试从表中复制数据。Then you can attempt to copy data out from tables.

可纠正的错误和延迟的事务Correctable errors and deferred transactions

并非数据库恢复期间遇到的所有错误都会导致恢复失败和可疑数据库:Not all errors encountered during database recovery will result in a recovery failure and a suspect database:

首次打开数据库和/或事务日志文件时的错误发生在恢复之前。Errors when opening the database and/or transaction log files for the first time, occur before recovery. 此类错误的示例为 1720417207Examples of such errors are 17204 and 17207. 纠正这些错误后,可以继续进行恢复(但如果发生其他恢复错误,则不保证能完成恢复)。Once these errors are corrected, recovery may be allowed to proceed (but not guaranteed to complete if other recovery errors occur). 17204 和 17207 等错误不会导致可疑数据库。Errors such as 17204 and 17207 do not result in a SUSPECT database. 实际上,发生这些问题时,数据库的状态为 RECOVERY_PENDING。In fact, the status of the database is RECOVERY_PENDING when these problems occur.

即使发生页面级别错误,SQL ServerSQL Server 也可以完成恢复,并仍保持事务一致性。SQL ServerSQL Server allows recovery to complete even when a page-level error occurs and will still maintain transactional consistency. 此过程减少了导致可疑数据库的场景数量。This process has reduced the number of scenarios resulting in a SUSPECT database. 这种概念称为延迟的事务This concept is referred to as deferred transactions.

如果恢复期间遇到的错误表明数据库页面存在问题(例如校验和错误或消息 824),则可以在错误待处理的情况下完成恢复。If the error encountered during recovery indicates a problem with a database page, for example as a checksum error or Msg 824, recovery may be allowed to complete with errors pending. 如果未提交事务,页面上的错误可能会引发延迟的事务,从而允许完成恢复。In the case where a transaction is uncommitted, an error on a page can result in a deferred transaction allowing recovery to complete.

以下错误日志条目展示了这样一个示例:在恢复期间遇到消息 824 错误,但由于延迟的事务,恢复得以完成。The following ERRORLOG entries show an example of a Msg 824 error encountered during recovery but recovery was allowed to complete with a deferred transaction. 请注意,这种情况下不存在错误 3414,并且请留意数据库恢复已完成的消息:Note the absence of Error 3414 in this situation and the message that recovery has completed for the database:

2010-03-31 19:17:18.45 spid7s      SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xb2c87a0a; actual: 0xb6c0a5e2). It occurred during a read of page (1:153) in database ID 13 at offset 0x00000000132000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\mydb.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.   
2010-03-31 19:17:18.45 spid7s      Error: 3314, Severity: 21, State: 1.   
2010-03-31 19:17:18.45 spid7s      During undoing of a logged operation in database 'mydb', an error occurred at log record ID (25:100:19). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2010-03-31 19:17:18.45 spid7s      Errors occurred during recovery while rolling back a transaction. The transaction was deferred. Restore the bad page or file, and re-run recovery.   
2010-03-31 19:17:18.45 spid7s      Recovery completed for database mydb (database ID 13) in 2 second(s) (analysis 204 ms, redo 25 ms, undo 1832 ms.) This is an informational message only. No user action is required.   

如果要前滚提交的事务,可以将页面标记为不可访问(后续尝试访问该页面会导致消息 829),并且可以完成恢复。If a committed transaction is to be rolled forward, the page can be marked inaccessible (any future attempts to access the page result in Msg 829) and recovery can complete. 在这种情况下,若要纠正错误,必须通过从备份中还原页面或结合使用 DBCC CHECKDB 和修复来取消分配页面。In this situation, the error must be corrected by restoring the page from a backup or by deallocating the page using DBCC CHECKDB with repair.

另请参阅See also

ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL)
DBCC CHECKDB (Transact-SQL)DBCC CHECKDB (Transact-SQL)
完整数据库还原(简单恢复模式)Complete Database Restores (Simple Recovery Model)
延迟的事务Deferred Transactions
MSSQLSERVER_824MSSQLSERVER_824
sys.databases (Transact-SQL)sys.databases (Transact-SQL)