Friday, March 14, 2014

Cannot start MSSQLSERVER service - Error 3414 - Solved

The actual problem was that the SQL server started but then stopped a few seconds later, causing the Windows Error 3414 to appear.

The 3414 error listed in the error log indicates that there are corrupt transactions that cannot be rolled back or forward.


You can find the following in Event viewer 

Application
Event ID: 9003
The log scan number (xx:xxx:x) passed to log scan in database 'model' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. 

System

Event ID: 7024
The SQL Server (MSSQLSERVER) service terminated with service-specific error 3414 (0xD56).

This is due to model.mdf corrupted you can find the same in ERRORLOG 

You can find it in Path Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

It could occur due to damaged controller, antivirus application, disk problems and other such issues. 


I tried to repair model.mdf with commercial MDF Repair products but model.mdf can't be repaired using this tool, still error occurred. I don’t find any information regarding the responsible issues,


So restoring from the last clean backup you have is the only option( Normally we won't take model, master mdf files). You can copy model.mdf and modellog.ldf from working SQL server instance from another server. (for safer you can backup a copy of model.mdf and modellog.ldf and place outside the \Data Folder.


Now try starting the SQL Service in services.msc. It worked for me :)

3 comments:

  1. There is one more solution to fix SQL error 3414. Get more information from http://www.sqlrecoverysoftware.net/blog/sql-error-3414.html

    ReplyDelete
  2. look at journal event , take name of Database that cause probleme
    If the error occur on System Database (master,msdb or model)
    just copy the (mdf+ldf) from %program files%Microsoft SQL Server\MSSQL10_50.SQLSERVER\MSSQL\Binn\Templates
    and paste (replace) it in folder
    Microsoft SQL Server\MSSQL10_50.SQLSERVER\MSSQL\DATA

    ReplyDelete
    Replies
    1. Dear Chakib Hali
      thanks for help...
      just face the error and your solutions save my day..

      Delete