How to troubleshoot when SQL Server Services don't start
Top 3 reasons the SQL server services won’t start.
Reason # 1:Service account password changed but not updated on the server where SQL Server instance is installed.
This is one of the most common cause where service account password has been changed by domain admin or SQL Admin but this information is not updated in SQL Server Services.
Hereis the error which we would see if we try to start using Services.
---------------------------Services---------------------------Windows could not start the SQL Server (MSSQLSERVER) service on Local Computer.Error 1069: The service did not start due to a logon failure.---------------------------
System Event logs should show below
Log Name: System
Source: Service Control Manager
Date: <Date Time>
Event ID: 7000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: MyServer
Description:The SQL Server (MSSQLSERVER) service failed to start due to the following error:
The service did not start due to a logon failure.
Solution: We need to update the password in services.The right way to do itisto use SQL Server Configuration Manager and type in new password(under Log On tab).
Reason # 2:Startup parameters have incorrect file path locations.
This is another common cause of SQL Server Service startup failure.Let’s assume that master database is located on a drive and files of the database (master.mdf and/or mastlog.ldf) are not available. Since master database is a system database, SQL Service would fail to start. If we try to start SQL via services, we will get below error.
---------------------------
Services
---------------------------
Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 17113.
---------------------------If we attempt the same via configuration manager, we get standard error which doesn’t explain much.
---------------------------
SQL Server Configuration Manager
---------------------------
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.
---------------------------
Whenever we get such errors, we should start looking at SQL Server ERRORLOGs are defined under start-up parametersor application event log.We can look at SQL Server Configuration Manager and look for Startup parameter having name -e as shown below (for SQL 2014):
We can open ERRORLOG using notepad or any other text editor. Here is the snippet which shows the problem.
<Date Time> Server Error: 17113, Severity: 16, State: 1.
<Date Time> Server Error 2(The system cannot find the file specified.)occurred while opening file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
As highlighted above, we can see that SQL is not able to find the file master.mdf.
Below is another example of error for model database.
<Date Time>spid9s Error: 17207, Severity: 16, State: 1.<Date Time>spid9s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.)occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.<Date Time>spid9s File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf" may be incorrect.<Date Time>spid9s Error: 945, Severity: 14, State: 2.<Date Time>spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details.<Date Time>spid9s Could not create temp db. You may not have enough disk space available. Free additional disk space by deleting other files on the temp db drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the temp db files could not be initialized.
Solution:First we need to find out the correct location of the files. Once files are identified, either put them into the location where SQL Server wants or ALTER the database to point to correct location.If there is an issue with model or temp db database, then we need to start SQL Server using trace flag3608.
Reason #3:System database files not available -accidental deletion or corruption due to disk failures.
If files are missing or corrupted for system databases (master and/or model) SQL Server service would not start. ERROR LOG (mentioned earlier)would contain the exact database name and file name which has the problem.Here are few snippets of error pointing to corruption of system databases.
<Date Time>spid5s Starting up database 'master'.
<Date Time> spid5sError: 9003, Severity: 20, State: 1.
<Date Time> spid5sThe log scan number (216:72:1) passed to log scan in database 'master' 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.
<Date Time>spid5s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
Depending on which database file is corrupted, we need to take appropriate action. If master database files are corrupted (error above)then we need to rebuild the master database and restore it from the backup. If issue exists with other system databases, then SQL can be started via trace flag and they can be restored.