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

屬性Attribute Value
產品名稱Product Name SQL ServerSQL Server
事件識別碼Event ID 34143414
事件來源Event Source MSSQLSERVERMSSQLSERVER
元件Component SQLEngineSQLEngine
符號名稱Symbolic Name REC_GIVEUPREC_GIVEUP
訊息文字Message Text 復原時發生錯誤,導致資料庫 '%.*ls' (資料庫識別碼 %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

並非在資料庫復原期間遇到的所有錯誤都會導致復原失敗與 SUSPECT 資料庫: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 的錯誤不會導致 SUSPECT 資料庫。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 即使發生頁面層級錯誤,也可讓復原完成,且仍會維持交易一致性。allows recovery to complete even when a page-level error occurs and will still maintain transactional consistency. 這個流程減少了會導致 SUSPECT 資料庫的案例數量。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)