MSSQL Server not starting after fresh installation

After the installation of Microsoft SQL Server 2014 Express I encountered a problem that the services could not be started. It seems that the installation have set the paths to the model, MSDB and tempdev to the wrong location. After uninstall the SQL Server instance and reinstalling it, the problem persists. No settings are altered in the setup properties, also I could not reproduce this same error on another server even using the same deployment image for the Azure VM. The solution is to change the paths to those 3 databases to the right location with starting the instance in the master-only recovery mode.

First thing to do is to start the command prompt with elevated rights.

When it is started we need to start MSSQL with the following command:

040715_1454_MSSQLServer1

Note that MSSQLSERVER is the instance name of the service that encounter the problem.

040715_1454_MSSQLServer2

Connect to the instance by using Windows Authentication to interactively run Transact-SQL with the following command:

Or with the instance name as:

Now you want to check with:

What is wrong with the file paths. Look up on the server for the right location, for SQL Server 2014 Express x64 it is in my case “C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA“.

040715_1454_MSSQLServer3

With the following command I change the path of the files to the right location:

Use exit to close the SQLCMD, stop the MSSQLSERVER instance in the master-only recovery mode and start the instance normal.

040715_1454_MSSQLServer4

Then I launched the SQL Management Studio to check if everything is working as, but the users I provided during the setup as administrators of the instance can’t logon. Also the ‘sa’ user is not able to logon to the instance.

040715_1454_MSSQLServer5

Here is the solution to reset the ‘sa‘ users password.

Again open the elevated command prompt and start the instance in the master-only recovery mode.

040715_1454_MSSQLServer6

After that use the following command to reset the password of the ‘sa’ user.

040715_1454_MSSQLServer7

Restart the service of the MSSQL instance. Start the SQL Management Studio and connect with SQL Server Authentication.

040715_1454_MSSQLServer8

Now you can create the other users you need to provide access to this instance.

040715_1454_MSSQLServer9

Some of the errors in the eventlog:

 

  • lance

    This saved me, thanks.

  • César A.

    Thank you for taking the time to look into this issue and explain how to solve it in such a detailed way.

    To clarify. If the instance name is “SQLEXPRESS” then to start the SQL Service you will
    probably use:

    NET START MSSQL$SQLEXPRESS /f /T3608

    Then to connect to the instance you should use:
    SQLCMD –S .SQLEXPRESS

    Regards.

  • Thanks a lot. Branko.
    I have the same issue and this post really saved me a lot of time searching the answers.

  • Alex

    Thanks very much Branko!
    Spent hours trying to figure it out, finally found your post.
    Was easy to follow your instructions to the solution!

  • Sudarev

    Thank you MAN! You realy helped me. BTW Does anyone know what is the reason of this weird situation?

  • Chris

    great post, absolutely saved my morning today.

    you might want to add the sqlcmd to create/add AD user “from windows” for ppl who have sql auth disabled (and apparently “enable” doesnt work)

    • BVucinec

      Will make a separate post about this, thanks for the heads-up.

  • Thank you very much for this post. It is great step by step working sollution. You saved me hours of work.

  • Kevin Lewis

    Nice happened with an Enterprise edition installation for me.

    FYI, it does look like it resets all the logins you would have requested in the installation, so if you did a windows only authentication, or you want to sort out the sa login later, you could could simply add the windows login to the instance.

    Open the elevated command prompt and start the instance in the master-only recovery mode. Then run sqlcmd -s .

    Once at the sqlcmd prompt run the following

    CREATE LOGIN [DOMAINMyAccount] FROM WINDOWS
    GO
    EXEC sp_addsrvrolemember ‘DOMAINMyAccount’,’sysadmin’
    GO
    EXIT

    Then restart the service.

    If you are using anything else than Express, you may find you have some trouble with the SQL Agent connecting.

    N.B This is not applicable for Express whilst you have a SQL Agent Service installed, you will not be able to use it as its not a component that is available for Express usage.

    What I found out is that SQL Agent is turned off.

    In SSMS run:

    RECONFIGURE WITH OVERRIDE;
    GO
    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    sp_configure ‘Agent XPs’, 1;
    GO
    RECONFIGURE
    GO

    You may need to also add the service account to the server, as ever consult the error logs.

  • Kevin Lewis

    May also be worth a shot to see if you can rebuild the system databases – if.when I find this again I’ll give it a go.

    From the installation media:

    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

    • BVucinec

      Thanks for your comments.

  • So glad to find this article. So upset that Microsoft obviously has placed some major bugs in SQL 2014. These bugs are still there in community preview of SQL 2016.

  • David Arnold

    I am having this same problem, but unfortunately this is not helping me. This is a fresh install of SQLExpress 2014. Upon startup the SQL Server service fails and leaves an error in the log indicating it cannot locate “E:sql12_main_t.obj.x86Releasesqlmkmastrdatabasesmkmastr.projmodellog.ldf” which is odd because this path is nonsense. My system has no drive “E:” let alone the rest of this path. All of the settings in the Instance settings as well is in the parameters section of the registry are correct for the location of the Master database and the logs but still this error persists. I would love to execute this solution but I cannot get past the first step of starting the service…any help would be appreciated:

    Server = Server 2012 R2

    • BVucinec

      Have you installed the SQL Server as instance? See the 2nd comment on this page (from César A.) for a how to start the service with a different instance name.

    • Simon Yates

      I also could not get past the first step, while I am sure you’ve sorted it now, for others I had to change the account the SQL service was running under to the local system account rather than the SQL account created on installation. I could then follow these instructions to solve the issue.

  • Problem is still there on 2016 Express. This solution worked for me.

    • Thanks for your comment, have not seen it myself on SQL 2016 Express.

  • Arago

    Thanks! This just fixed an installation of 2016 Enterprise for me.

  • This seems to be related to the installation option to allow user instances. If that’s unchecked, the user specified to run the SQL Server service isn’t able to start the instance. Changing the user to administrator (not recommended) can solve the problem if the service doesn’t even start at the command line.

  • Senthil

    Thank you very much. The solution worked for me. I had to run the service as LocalSystem account to get past step 1. Otherwise, the service would start and stop immediately.