Get Alerted When Your SQL Server Restarts with sp_SendStartupMail

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_SendStartupMail 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:

Previous Post
What If You Really DO Need to Shrink a Database?
Next Post
Announcing Saturday & Sunday Office Hours.

19 Comments. Leave new

  • Michael Orechoff
    July 15, 2020 9:12 am

    Good idea. I use sql agent restarted to tell me when a server restarts. I created a Job that sends me an email.

    Reply
  • John Vandermey
    July 15, 2020 9:25 am

    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.

    Reply
  • 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.

    Reply
    • 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.

      Reply
      • 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!

        Reply
        • 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. 😉

          Reply
  • Terry Schmitt
    July 15, 2020 11:50 am

    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)?

    Reply
    • 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!

      Reply
  • Lawrence Lefebvre
    July 15, 2020 12:29 pm

    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.

    Reply
    • Sure, by all means, if you can figure out how to do that, we’d all love to see that one.

      Reply
      • John Vandermey
        July 15, 2020 12:36 pm

        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).

        Reply
  • Tracy Boggiano
    July 15, 2020 12:58 pm

    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.

    Reply
  • Mark Poynter
    July 15, 2020 5:16 pm

    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.

    Reply
  • John Vandermey
    July 16, 2020 6:00 am

    Except for those pesky security patches that have all my servers rebooting randomly today! 🙂

    Reply
  • 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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}