SQL Server service startup failure
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.
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;
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:
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');
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]
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.