Error starting SQL instance after fresh install (error %%945)

Error starting SQL instance after fresh install (error %%945)

After doing a fresh installation of SQL2014 Express on a Windows 2012R2 standard server i got an error while connecting to the server instance.
After looking in the eventlog i found these messages stating that the SQL instance service terminated
(The SQL server (SQLEXPRESS) service terminated with the following service -specific eror: %%945)

System log:
systemlog

Application log:
applog 2

applog 1

The errors in the application log seem odd because the server had no e drive!
During testing i found out that could only start the instance in single user mode and when i tried connecting through SQL management tools the service would crash.

So I tried figuring out where the sql instance installed all the databases by using these commands

  • NET START MSSQL$SQLEXPRESS /f /T3608
  • SQLCMD -S .\SQLEXPRESS

once the connection was made to the sql instance

  • SELECT name, physical_name, state_desc FROM sys.master_files ORDER BY database_id;
  • go

Then the following output was generated

check database

After reviewing the output it looks that some of the databases where configured on a non existing drive for reasons unkown..
I used these commands to get the databases on the right drive/directory.

  • NET START MSSQL$SQLEXPRESS /f /T3608
  • SQLCMD -S .\SQLEXPRESS

Once the connection was made to the SQL instance I used these commands to set the correct drives/path settings

  • ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\model.mdf’);
  • ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\modellog.ldf’);
  • go

Be aware that the model database needs to be set first otherwise the other databases wont’t be changed and you’ll get lots of errors!

  • ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\tempdb.mdf’);
  • ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\templog.ldf’);
  • ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\MSDBData.mdf’);
  • ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\MSDBLog.ldf’);
  •  go
  •  exit

After that I restarted the SQL service and it would now start without issues!

Next step was connecting with SQL management studio where i got this error (with sa account and windows integrated authentication)
logon error

After tripple checking all the passwords it seems that the SA account wasn’t created properly so that needs to be fixed..

So here’s how thats done:
Restart the database in single user mode

  • NET STOP MSSQL$SQLEXPRESS
  • NET START MSSQL$SQLEXPRESS /m

Connect to the master database in single user mode and create a new user with the correct database admin rights

  • SQLCMD -S .\SQLEXPRESS
  • CREATE LOGIN noob WITH PASSWORD = ‘whatever’
  •  GO
  •  sp_addsrvrolemember ‘security’, ‘sysadmin’
  •  GO
  • quit

Restart the sql service in normal mode

  • NET STOP MSSQL$SQLEXPRESS
  • NET START MSSQL$SQLEXPRESS

Now connect to the instance with the SQL management tool and user noob and whatever as username and password. You can then change the SA users password and add the domain members you want with the specific required rights. After that is done don’t forget to disable or remove the noob account for security reasons!

Tracing back the installation steps I found out that during the installation there was a warning indicating there was a problem executing sql scripts.

Leave a Reply