When a database fails to recover, an error like the following is written to the ERRORLOG or Windows Application Event Log with EventID=3414:
Error: 3414, Severity: 21, State: 1.
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)
The reason for the recovery failure is typically an error that precedes Error 3414 in the ERRORLOG or Event Log.
When a database fails recovery with this error, the database status is set to SUSPECT. You will see this 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
The cause of the recovery failure is found in preceding errors in the ERRORLOG where the line in the log file has the same spid<n> value. For example, the following is a recovery failure due to a checksum error when trying to read a log block to roll forward a transaction:
2010-03-31 17:33:13.00 spid15s Error: 824, Severity: 24, State: 4.
2010-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.
2010-03-31 17:33:13.16 spid15s Error: 3414, Severity: 21, State: 1.
2010-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 Resolution section below.
The error message says to "diagnose recovery errors and fix them, or restore from a known good backup". In reality, restoring from a backup is your first, best option to resolve this problem. However, if you cannot recover from a backup, you have two options:
- Use the emergency repair method provided by DBCC CHECKDB
- Try to copy out as much as is possible to another database
The first method is probably your best bet 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. The steps to proceed with emergency repair are described in the section titled Resolving Database Errors in Emergency Mode in the SQL Server Books Online under the DBCC CHECKDB command.
If this method does not work and you want to try and copy data to another database, the only way to get access to the database is to first put the database in emergency mode by using the ALTER DATABASE <dbname> SET EMERGENCY command.
Not all errors encountered during database recovery will result in a recovery failure and a suspect database:
If errors are encountered when first opening the database and/or transaction log files this happens before recovery and will result in errors such as Msg 17204 and 17207. Once these errors are corrected recovery can be allowed to proceed (but not guaranteed to complete if other errors effecting recovery occur). Errors such as 17204 and 17207 do not result in a SUSPECT database. In fact, the status of the database is RECOVERY_PENDING when these problems occur. For more information about troubleshooting Error 17204 or 17207 please see the following article: How to troubleshoot Error 17204 and 17207 in SQL Server.
SQL Server 2005 introduced a new concept to allow recovery to complete even when a page level error is encountered and still maintain transactional consistency. This has reduced the number of scenarios resulting in a SUSPECT database. This concept is generally referred to as deferred transactions.
If the error encountered during recovery is a problem with a database page such 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 like checksum can result in a situation called a deferred transaction allowing recovery to complete. To learn more about deferred transactions and how to recover from them, see the section titled Deferred Transactions in the SQL Server Books Online.
The following ERRORLOG entries shows an example of a Msg 824 error encountered during recovery but recovery was allowed to complete with a deferred transaction. 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 Error: 824, Severity: 24, State: 2.
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.
In the case of a committed transaction that should be rolled forward, the page can be marked inaccessible (any future attempts to access the page result in Msg 829) and recovery can be allowed to complete. In this situation, the error must be corrected by restoring the page from a backup or deallocating the page by using DBCC CHECKDB with repair.
NOTE: Deferred transactions is an advanced feature of SQL Server only available in Enterprise Edition and has some limitations and requirements:
- The database must be using the FULL or BULK-LOGGED recovery model.
- At least one database and log backup must have been completed for the database
- This does not apply to errors encountered during a rollback of a transaction after the database is online. (e.g. a runtime error)
- Does not work for recovery failures during a database attach
- Some transactions such as system transactions (Ex. page allocation) are not supported for deferred