Small Town Sysadmin
Free CJIS checklist

Field notes

Field note: the SQL server that "crashed" every night

A database kept going down overnight and needed a manual restart every morning. The logs said it wasn't crashing at all. Here's what was really going on.

For two days straight, a small office’s SQL Server went down overnight. Each morning the accounting and HR apps that depended on it were dead until someone restarted the SQL service by hand. It looked like a crashing database. It wasn’t.

What it looked like

  • The SQL Server service was stopped every morning.
  • Restarting the service fixed everything, until the next morning.
  • The server itself seemed fine during the day.

What I checked, in order

1. SQL’s own error log. If SQL Server were crashing, its error log would usually say so. It didn’t. The log showed clean, normal operation right up until the service stopped, plus regular backups from the backup software every few hours.

2. The Windows service events. In the System event log, events 7031 and 7034 record a service ending unexpectedly. There weren’t any for SQL Server. So the service wasn’t dying on its own.

3. Shutdown and reboot events. This is where it got interesting:

  • Event 41 and 6008 (unexpected shutdowns) showed two older hard crashes from weeks earlier. Worth noting, but they didn’t line up with the recent problem.
  • Event 1074 (a planned, clean restart, including who asked for it and why) showed the server being rebooted on purpose at about 11 PM on both nights. The restart came from the SYSTEM account, with a comment saying it was “maintenance.”

The server wasn’t crashing. Something was rebooting it every night.

4. Scheduled tasks. Tracing the restart back through the scheduled tasks led to our own RMM agent (the remote monitoring and management software IT companies use to patch and watch machines). Its patch policy was rebooting the server nightly.

Why SQL didn’t come back

A reboot alone shouldn’t take SQL down for good, since the service is set to start automatically. But after these reboots, SQL Server wasn’t reliably starting back up. The most likely explanation is a startup race: services start in parallel at boot, and SQL can lose out to things it depends on, then give up.

The fix

Two changes, one immediate and one structural:

Make SQL Server recover on its own. Set the service to delayed automatic start, so it starts after the boot rush, and tell Windows to restart it automatically if it ever stops:

sc config MSSQLSERVER start= delayed-auto
sc failure MSSQLSERVER reset= 86400 actions= restart/30000/restart/60000/restart/60000

(The spaces after start=, reset=, and actions= are required. That’s a quirk of the sc command.)

Stop rebooting database servers every night. The longer-term fix is in the RMM: set the patch policy to reboot only when an update actually requires it, and move servers that host databases into their own policy, separate from ordinary workstations, with a planned maintenance window.

What I took away from it

  • “Crashing” is a guess until the logs agree. SQL’s error log and the service events ruled out a crash in about ten minutes.
  • Event ID 1074 answers “who rebooted this box, and why.” It’s one of the most useful events in Windows.
  • Your own tools can be the culprit. The thing restarting the server was the software meant to keep it healthy.
  • Critical services should recover without you. Delayed start and service recovery actions are two minutes of setup that save a lot of early-morning calls.

Sources

All articles