This has happened twice for me in last month so here’s how I document it since if something happens twice will happen more again.
Here’s how it started: I paved my machine and installed SQL 2012 Express for one of my projects. Once the installation went its smooth way and finished at the last step I got a warning that the Windows service can’t be started. Interestingly the installation was not rolled back and I was left with a SQL service that wouldn’t start. Event viewer wasn’t particularly helpful either. He’s how I’ve done my investigation which showed me the exact direction I needed to go.
To understand the issue, I had to run the SQL process somehow and see what’s happening. How do you do this? Pretty easy! You just check how Windows service is running:
So just copy that command into a command prompt, but before you run, we need to force the instance to start and put it in single user mode, so add the following switches to it:
C:\PathToYourSQLInstance\MSSQL\Binn\sqlservr.exe -sMSSQLSERVER /f /T3608
Note that I installed my database as the default instance. This will be a repeating assumption.
Once you run this, you get to see the logs which will tell you exactly what’s happening under the hood. Here are the interesting bits:
2015-09-01 19:37:19.92 spid12s Database ‘model’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2015-09-01 19:37:19.89 spid12s Starting up database 'model'. 2015-09-01 19:37:19.89 spid12s Error: 17204, Severity: 16, State: 1. 2015-09-01 19:37:19.89 spid12s FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\model.mdf for file number 1. OS error: 3(The system cannot find the path specified.). 2015-09-01 19:37:19.89 spid12s Error: 5120, Severity: 16, State: 101. 2015-09-01 19:37:19.89 spid12s Unable to open the physical file "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\model.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
See that E:\ drive and system can not find the path for model database? That looks to be the issue. I don’t even have an E: drive to begin with!
How do I fix it though? At this stage since the service is up in forced mode, you can connect to it. So bring up another command prompt and run this:
SQLCMD -s .
Now from here, you can run the following statement:
SELECT name, physical_name, state_desc FROM sys.master_files ORDER BY database_id; GO
This will show you paths to the data file and log file for your databases. Mine was something like the following, which clearly shows that my main databases (temp, model, msdb) all have wrong paths:
.E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\tempdb.mdf .ONLINE .E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\templog.ldf .ONLINE
So the fix would be to change that path that’s pointing to the non-existing E: drive to a proper location, which these statements would take care of:
ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\model.mdf'); GO ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\modellog.ldf'); GO ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\templog.ldf'); GO ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf'); GO ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf'); GO
After doing this, I could start the service except I couldn’t login with my “sa” account. Fortunately you can use the same tecnique to reset the SA account’s password. Again run the service and connect to it like the above and run these:
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master] GO ALTER LOGIN [sa] WITH PASSWORD=N'yoursapasswordgoeshere' GO
And that’s that. One more thing to note is that even when I uninstalled SQL 2012 and installed SQL 2014 on the machine, exact same thing happened again. Hope this saves someone from a headache.