SQL database engine service startup failure: Troubleshooting Checklist
Hi all,
In this article, we are covering the common scenarios of SQL Service startup failures and troubleshooting approach to resolve these issues:
1. For standalone instance of SQL, always use SQL Server configuration manager to start the SQL database Engine service. For Clustered instance of SQL, always use Fail over Cluster manager tool to start the SQL Database engine service.
Error while starting SQL service using Configuration manager: 
Error While starting service from services.msc: 
2. Start SQL Server from the command prompt to rule out issues such as permissions or WMI issues or service account related issues. When you start SQL Server from the command prompt, it starts as an application, not a service, with the credentials of the currently logged-in Windows user.
To start SQL Server from command prompt:
Locate the SQL Database engine service and copy “Path to executable” section.
Open command prompt in run as administrator mode and paste the above path and use -c parameter as mentioned below:
If you are successfully able to start the SQL Service, then it’s an indication that the SQL Service account used to start the SQL Service (permission, password, locked out) need to be reviewed.
If you are not able to start the SQL Service using command prompt as well, review the Error log.
3. Check if an Errorlog entry is getting created when the SQL Service is started:
If it is not generated,
- Check startup parameters & ensure the path and files exists (master.mdf, Error log, mastlog.ldf).
- Check registry for startup parameters.
4. Check the permissions on startup parameters to check if SQL Service account has read permissions on master database files and write permission to create Error log file.
For example, permission error on master database file:
2018-02-27 12:10:22.65 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\mastlog.ldf
2018-02-27 12:10:22.65 Server Command Line Startup Parameters:
-s "SQL2017"
2018-02-27 12:10:22.65 Server Error: 17113, Severity: 16, State: 1.
2018-02-27 12:10:22.65 Server Error 5(Access is denied.) occurred while opening file 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\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.
2018-02-27 12:10:22.65 Server SQL Server shutdown has been initiated
To troubleshoot permission issues, take process monitor while reproducing the issue and review ACCESS DENIED reported by sqlservr.exe
Reviewing the event properties will show the user and the set of permissions needed. Accordingly setup the required permissions.
5. From SQL 2012, Configuration manager has a new tab for Startup parameters. This will assist in scenarios where SQL service startup issues are reported because of parameters not being added correctly (examples include missing semi-colon, white-spaces etc).
6. While reviewing the Error log, check for “Recovery is complete” message like below:
2018-02-27 12:20:42.55 spid8s Recovery is complete. This is an informational message only. No user action is required.
Even though startup parameter values are intact, if model and tempdb database files are not accessible, SQL Service will not start. Similarly, MSDB database files are needed for SQL Agent service startup.
Review the SQL Error log to check if there are any issues in accessing the system database files.
For example, in the below error log, model database is not accessible:
2018-02-27 12:40:43.04 spid11s Error: 17204, Severity: 16, State: 1.
2018-02-27 12:40:43.04 spid11s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\model.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
2018-02-27 12:40:43.04 spid11s Error: 5120, Severity: 16, State: 101.
2018-02-27 12:40:43.04 spid11s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\model.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
2018-02-27 12:40:43.07 spid11s Error: 945, Severity: 14, State: 2.
2018-02-27 12:40:43.07 spid11s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2018-02-27 12:40:43.07 spid11s SQL Server shutdown has been initiated
7. If the SQL Service startup fails with the error “logon failure”, review the Windows event viewer system log:
User name and password is incorrect:
The MSSQL$SQL2017 service was unable to log on as machinename\sqluser with the currently configured password due to the following error:
The user name or password is incorrect.
To ensure that the service is configured properly, use the Services snap-in in Microsoft Management Console (MMC).
User Account is locked out:
The MSSQL$SQL2017 service was unable to log on as machinename\SQLUSER with the currently configured password due to the following error:
This user can't sign in because this account is currently disabled.
To ensure that the service is configured properly, use the Services snap-in in Microsoft Management Console (MMC).
Based on the above error, accordingly take an appropriate action to reset the password/unlock the account.
8. SQL Service may not start if the System databases are corrupt.
Starting up database 'master'.
Starting up database 'model'.
Starting up database 'msdb'.
SQL Server is now ready for client connections. This is an informational message; no user action is required.
Error: 5172, Severity: 16, State: 15.
The header for file 'C:\Databases\MSSQL14.SQL2017\MSSQL\DATA\model.mdf' is not a valid database file header. The PageAudit property is incorrect.
Error: 945, Severity: 14, State: 2.
Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
If the system databases are corrupt, supported options to recover the system databases are either restore the system databases from last known good backup or rebuild the master database.
9. Check if SQL Service startup issue is due to OS 32 error (The process cannot access the file because it is being used by another process.):
For example:
2011-04-16 07:52:49.32 spid5s Error: 17204, Severity: 16, State: 1.
2011-04-16 07:52:49.32 spid5s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\mastlog.ldf for file number 2. OS error: 32(error not found).
In the above scenario, some other process/application has a lock on master database ldf file and this isn’t allowing SQL to take access, hence the service fails to start. To find out which process it is you can use either process explorer or handle.exe tool. The usual culprits are Anti-Virus or Anti-Malware services.
10. Check if SQL Server is configured for SSL encryption. If there are any SSL certificates loaded in SQL Server and certificate doesn’t meet the requirements of SQL Server, SQL service wont start.
For example:
2018-02-27 12:36:49.39 spid16s The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
2018-02-27 12:36:49.39 spid16s Error: 26014, Severity: 16, State: 1.
2018-02-27 12:36:49.39 spid16s Unable to load user-specified certificate [Cert Hash(sha1) "7031C0BF54FEFFD42BB7B7589BAF9A92E57A4A21"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
2018-02-27 12:36:49.39 spid16s Error: 17182, Severity: 16, State: 1.
2018-02-27 12:36:49.39 spid16s TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2018-02-27 12:36:49.39 spid12s Starting up database 'mssqlsystemresource'.
2018-02-27 12:36:49.39 spid16s Error: 17182, Severity: 16, State: 1.
2018-02-27 12:36:49.39 spid16s TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
To check if certificate is loaded in SQL, refer the below registry key:
Check in SQL Configuration manager, if the certificate used is listed in drop down list.
If SSL encryption for SQL Server is not needed, then remove the thumbprint entry from the SQL registry and restart the SQL Service. If SSL encryption to SQL is needed, then SSL certificate used for encryption need to evaluated to check if it meets the pre-requisites.
11. You can also use the below parameters as startup parameter to troubleshoot further:
Hope the above steps shared help you troubleshoot SQL service startup issues.
Please share your feedback, questions and/or suggestions.
Thanks,
Don Castelino | Premier Field Engineer | Microsoft
Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.