When your SQL Server restarts, how do you find out?
I’ve seen a few senior DBAs do a really smart trick: they write a stored procedure to send them an email, and then they mark that stored procedure as a startup stored procedure so that it runs automatically whenever SQL Server starts up. (You could also try this with an Agent job scheduled to run at Agent startup, but … Agent may not actually start.)
So I wrote sp_SendStartupEmail to do just that, and the body of the email lists any databases with unusual states. It looks like this:
You can get it from my Github repo here, but it won’t be part of the First Responder Kit – I just wanted to put it out in Github so y’all could check in changes if you wanted. The documentation is in that script. (No, it’s not self-documenting code, hahaha – the documentation is in the comments.)
You can watch me write it during one of my recent Twitch / YouTube / Facebook streams:
25 Comments. Leave new
Good idea. I use sql agent restarted to tell me when a server restarts. I created a Job that sends me an email.
I can see where this will be extremely useful for DBAs that are not also server admins. I created a PowerShell script that sends a server startup email when the server itself reboots. This is triggered at the OS level. I did this because OS patches would get pushed and the server rebooted while data warehouse jobs were loading large chunks of data, or in the middle of backups. I have various functions that kick off to tell me tables weren’t loaded, or backups are aging. The startup email lets me know why.
Like Michael Orechoff I have a SQL Server Job that has a schedule type of “Start automatically when SQL Server Agent starts”. Since our workload requires SQL Agent to be running, it is equivalent to the engine restarting and we want to know about it so it works for us. I’ve also done a startup procedure before and that works just as well.
Yeah, I’ve just had some really rough cases where SQL Server restarts but the OS doesn’t, and where SQL Server restarts but Agent doesn’t come online. For me, the most important thing is the SQL Server service, so that’s where I focus my monitoring. If y’all are in different places, that’s totally cool too – glad you’ve found something that works for you.
SQL Server started but the OS didn’t? I… I had no idea this was a thing… Sounds like an absolute horror show. I may not sleep tonight. If you happen to have a blog post where you talk about how this happened (and how you solved it) please point me in that direction! Thanks!
Jeff – make sure to read carefully: “cases where SQL Server restarts but the OS doesn’t”
Every letter in that sentence is important. Take your time, sound the words out, and I have every confidence that you’ll do fine.
I’m kidding. I don’t have every confidence. 😉
(Just to make it brutally clear: your SQL Server service can restart WITHOUT the operating system going down. That’s what restarts means.)
Yep, I misread that. Sorry!
Hi Brent, good stuff as usual. I think the COUNT(*) when evaluating operators causes the code to pass that section even if there isn’t one (my situation). Perhaps an ELSE IF 0 = COUNT(*) (instead of NOT EXISTS) or NOT EXISTS and simply SELECT * (without the COUNT)?
Terry – thanks! Can you use normal Github protocols for changing the code – i.e., do a pull request? I’m going to be a stickler about this one – I don’t wanna be doing code troubleshooting in the comments. Hope that’s fair. Thanks!
Good to know when a servers starts but better to know when it goes down. This is not as easy as a start notification but I’d like to see a process for shutdown.
Sure, by all means, if you can figure out how to do that, we’d all love to see that one.
I have an OS level cmd file that checks all my servers for running services and emails me when it finds issues. Doesn’t trigger off of a shutdown event though. Just runs every hour. It’s mostly useful when a server reboots and a service fails to fire back up (like Agent).
Only gotcha is if you security compliance restrictions you can’t have start up stored procedures depending with some of them. I’ve done this before when I didn’t have another way and I didn’t have to adhere to security compliance.
Unfortunately this approach is not supported on SQL Database Managed Instances as it returns the error:
Msg 5869, Level 16, State 1, Procedure sp_configure, Line 166 [Batch Start Line 23]
Changes to server configuration option scan for startup procs are not supported in SQL Database Managed Instances.
Mark – yep, this post is about SQL Server, as I cleverly indicated in the title. 😉
Don’t worry. MS guarantees a very high uptime. No restart notification required!
Except for those pesky security patches that have all my servers rebooting randomly today! 🙂
I find out when sql stops working, because during unapproved patching, the server tech decided to delete some drives off the server for some other avante garde preventative maintenance reason.
I wonder about patch weekends and the amount of expected reboots and corresponding emails. Just curious, does anyone have any ideas on how to temporarily disable this?
Ken – for just-curious type questions, best to post at a Q&A site like https://DBA.stackexchange.com or https://sqlservercentral.com.
Just a heads up that if you Google sp_sendstartupemail (including the “e” on email – the name of the stored procedure), no results are found. If you Google sp_sendstartupmail (no “e”), you get this page. Is it possible to add the name of the stored proc to the header or tags on this page to make searches easier to find?
Ken – great catch, thanks! Fixed.
PowerShell or C# service connecting every minute and emailing on failure shouldn’t be too hard.
That assumes that the SQL Server service takes longer than 60 seconds to restart, which in many cases is an incorrect assumption.