Troubleshooting Error 3313, 3314, 3414, or 3456 (SQL Server)
Troubleshooting Error 3313, 3314, 3414, or 3456 (SQL Server)
http://technet.microsoft.com/en-us/library/ff713991(v=sql.100).aspx
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).
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.
![]() |
---|
If this error occurs for tempdb, the SQL Server instance shuts down. |
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.
![]() |
---|
When any of these error conditions is encountered, SQL Server typically generates three files in the SQL ServerLOG 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 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.
For a transient condition:
-
Attempt to bring the database online by executing the following ALTER DATABASETransact-SQL statement:
ALTER DATABASE <database name> SET ONLINE;
-
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.
-
If the database is online, execute the DBCC CHECKDBTransact-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:
-
Attempt to take a tail-log backup.
-
Restore the database from a full database backup, optionally followed by a differential database backup, using the RESTORE …WITH NORECOVERY Transact-SQL statement.
-
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.
-
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.
-
After the database comes online, run the DBCC CHECKDBTransact-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:
-
Transition the database into the EMERGENCY state by executing the following ALTER DATABASETransact-SQL statement:
ALTER DATABASE <database_name> SET EMERGENCY;
-
Review the output from the ALTER DATABASE statement and from the SQL Server error log.
-
Examine the state of the database in the sys.databases catalog view.
-
Perform a consistency check against the database using the DBCC CHECKDB statement to understand the nature and extent of damage.
-
After evaluating the output from DBCC CHECKDB, you can choose to execute DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option.
Caution
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.
-
![]() |
---|
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:
-
Review the SQL Server error log and the Windows Event logs for any system wide problems that can contribute to this error.
-
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.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2013-10-23 SQL2005解密已经被加密的存储过程
2013-10-23 使用SQLServer 2008的CDC功能实现数据变更捕获