This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
SQL Server service – Not Starting With Automatic Startup, “The system cannot find the file specified”, but starts fine manually
Question
This is a weird one, and I’ve run out of ideas for how to go about troubleshooting this. Here’s the issue, best as I can sum it up.
The SQL Server engine and Agent services are not started when the server first boots when they’re set to Automatic. Looked through the Event Viewer, and found these errors:
The MSSQLSERVER service failed to start due to the following error:
The system cannot find the file specified.
However, if I go into Services after boot and manually start the services, they start running without issue. More confounding is that this is not limited to a single server. They’re all part of clusters/AGs if that helps.
Dug through some various websites and postings, and here’s my comparison:
This is the same error that we see in the event log. When I looked into the properties of the service, I found that the path mentioned in “Path to executable” was an invalid path.
Does not apply here. I can start the service manually, and the paths in the service and registry are correct.
Domain account is being used (not a gMSA), and the password is set correctly.
asked 2021-10-29 by Sean White NT
Answer
This isn’t an entirely uncommon thing to happen. Based on the error message, it may be that your SAN storage isn’t fully online before the SQL Server service starts.
Windows starts up the "Automatic" services in "random" order, unless you explicitly set service dependencies, and the SQL Server service does not have any dependencies at all. However, storage availability (or lack thereof) can influence SQL Server’s ability to start.
Changing SQL Server to "Automatic (Delayed Start)" in the services control panel will inject a 2 minute delay into the service startup. In most cases, the additional 2 minutes is an acceptable delay, and makes a sensible solution if your SQL Server service is failing to start.
You can also use the "Recovery" tab for the SQL Server Service on the Services control panel to automatically restart in the event of failure. For example, you can set that on First & Second failure, the service restarts after 1 minute. Using this without delayed start can have approximately the same net effect–your service might start sooner, but might fail & have to attempt restarts.
answered 2021-10-29 by Andy Mallon