5

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:

https://blog.sqlauthority.com/2020/01/16/sql-server-event-id-7000-the-system-cannot-find-the-file-specified/

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.

Improve this question
1
  • On what kind of volume are your SQL Server binaries?
    – Eric Prévost
    Oct 29, 2021 at 19:29

1 Answer 1

Reset to default
11

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.

SQL Server Service recovery tab

Improve this answer
5
  • I'll give it a shot. It started happening on a "live" system, so can't try it out right now, but I'll report back next reboot.
    – Sean White NT
    Oct 29, 2021 at 20:51
  • 1
    I'd suggest changing the service properties now, so that the next reboot (even an unexpected one!) will benefit from the change.
    – AMtwo
    Oct 29, 2021 at 20:58
  • 1
    Wouldn't it be better to set the dependencies for the SQL Server Service to the FileSystem service they depend on?
    – rene
    Oct 30, 2021 at 9:41
  • 2
    If you can accurately determine the dependencies, and can guarantee that SQL Server will always depend on that service for starting, and you know to do it and document it in your environment, yes. But Delayed Start is simple and reliable.
    – David Browne - Microsoft
    Oct 31, 2021 at 15:47
  • Delayed start looks to have fixed the issue, so I'm marking this as the answer. Thanks very much.
    – Sean White NT
    Nov 10, 2021 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.