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.

1
  • On what kind of volume are your SQL Server binaries? Oct 29 '21 at 19:29
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

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. Oct 29 '21 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 '21 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 '21 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. Oct 31 '21 at 15:47
  • Delayed start looks to have fixed the issue, so I'm marking this as the answer. Thanks very much. Nov 10 '21 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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