• Article

Troubleshooting Error 3313, 3314, 3414, or 3456 (SQL Server)

An error during a redo, undo, or recovery operation on a SQL Server database places the database into the SUSPECT state. Such errors include 3313 (log redo error), 3314 (log undo error), 3414 (recovery error that prevents database restart), and error 3456 (redo error on a logged transaction).

Explanation

A redo, undo, or recovery error places the database into the SUSPECT state because the primary filegroup, and possibly other filegroups, are suspect and may be damaged. The database is unavailable, and user action is required to resolve the problem.

Note

If this error occurs for tempdb, the SQL Server instance shuts down.

User Action

A redo, undo, or recovery error can be caused by a transient condition or by a permanent failure that occurs every time that you attempt to start the database.

For information about the cause of a given occurrence of error 3313, 3314, 3414, or 3456, examine the Windows Event Log for a previous error that indicates the specific failure. The appropriate user actions depend on whether the information in the Windows Event Log indicates that the SQL Server error was caused by a transient condition or a permanent failure.

Note

When any of these error conditions is encountered, SQL Server typically generates three files in the SQL Server LOG folder. The SQLDumpnnnn.txt file contains advanced diagnostic information relating to the failures, including the details about the transaction and the page that encountered the problem. This information is typically used by the Product Support team to analyze the reason for the failure.

For a transient condition:

  1. Attempt to bring the database online by executing the following ALTER DATABASE Transact-SQL statement:

    ALTER DATABASE <database name> SET ONLINE;

  2. To determine whether the recovery finished successfully and the database came online, look at the SQL Server error log and the sys.databases catalog view.

  3. If the database is online, execute the DBCC CHECKDB Transact-SQL statement to verify whether the database is consistent.

For a permanent failure:

Look in the SQL Server error log for the error that you are troubleshooting (3313, 3314, 3414, or 3356), and review the messages that precede it to determine whether you can correct them manually. If you can correct the earlier errors, perform one of the following procedures:

  • Restore and verify the database (the recommend procedure), as follows:

    1. Attempt to take a tail-log backup.

    2. Restore the database from a full database backup, optionally followed by a differential database backup, using the RESTORE …WITH NORECOVERY Transact-SQL statement.

    3. If the database uses the full recovery model, apply all transaction log backups taken after the restore full, or differential, backup up to the point of failure, using RESTORE LOG … WITH NORECOVERY.

    4. When you have restored the database as closely as possible to the point of failure, recover the database by using RESTORE DATABASE <database_name> WITH RECOVERY.

    5. After the database comes online, run the DBCC CHECKDB Transact-SQL statement to verify whether the database is consistent.

  • Attempt to bring the database online by using the steps described for a transient error, earlier in this section.

  • Use emergency mode, as follows:

    1. Transition the database into the EMERGENCY state by executing the following ALTER DATABASE Transact-SQL statement:

      ALTER DATABASE <database_name> SET EMERGENCY;

    2. Review the output from the ALTER DATABASE statement and from the SQL Server error log.

    3. Examine the state of the database in the sys.databases catalog view.

    4. Perform a consistency check against the database using the DBCC CHECKDB statement to understand the nature and extent of damage.

    5. After evaluating the output from DBCC CHECKDB, you can choose to execute DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option.

      Warning

      Before using the option, carefully review the information about resolving errors in database emergency mode in the DBCC CHECKDB (Transact-SQL) topic of SQL Server Books Online.

Note

For information about responding to hardware issues that are relevant to error 3313, 3314, 3414, or 3356, see MSSQLSERVER_824.

Steps for Avoiding This Error

To avoid running into this situation again, do the following:

  1. Review the SQL Server error log and the Windows Event logs for any system wide problems that can contribute to this error.

  2. To rule out any known issues in the product that lead to this condition, apply the latest Cumulative Update for your version of SQL Server.