In a recent post, I explored how to handle the startup event of a SQL Server instance by using the sp_procoption system stored procedure. It has an optional parameter @OptionName. The MSDN documentation tells us the only value for @OptionName is 'startup'. Wait...What? A parameter that has only one valid value? If that's the case, why have the parameter at all?
The developer in me can guess how this came to be: the stored proc was probably created with the intention to handle more than just the startup event * I have no inside knowledge about this. It's pure speculation on my part. But support for other events was never implemented. And that's a shame. An event handler for shutdown is the feature I've wanted for a few years: .
SQL Server Shutdown
If 'shutdown' was valid for the @OptionName parameter, I know exactly what I'd do: configure sp_procoption to execute a stored proc that sends an email alert. It would be great to know when a SQL instance goes down, wouldn't it? Looking at the log file, we can see entries indicating the instance was shut down:
When you're not the one who writes the code, it's easy to say "It should be easy to add <insert your feature request here>". But, c'mon. This really *should* be easy. Right? 😉 Nevertheless, there's an issue that may be insurmountable to overcome.
Not So Graceful
My feature request would only work when the SQL Server instance shuts down "gracefully". And that doesn't always happen. Servers can lose power, hardware can fail, Windows sometimes crashes, SQL Server itself sometimes crashes, etc. And that presents a catch-22: how can a SQL instance tell us it was shut down when the sqlservr.exe process is no longer running? As an example, here's a look at consecutive log files from just before and after a hard shutdown:
The hard shutdown happened sometime between 3:42 and 3:48p. But there is no "SQL Server is terminating" log entry. So a shutdown event handler would seemingly be imperfect. But I'd take it anyway.