T-SQL Tuesday is a monthly SQL Server blogging event. One of us sets a topic for the month’s event, and we all have a week in which to write a blog post that fits that topic. This month’s event is hosted by Nick Haslam (Twitter|Blog), and he’s asking us to talk about Soylent Green.
Thankfully, as this isn’t a film I know, he’s put in an alternative, specifically asking us for “your most horrifying discovery from your work with SQL Server”.
Right. That’s the boiler-plate out of the way. Let’s talk messaging.
I’ve just had a call from a friend in the USA that reminded me of something that happened to me a while ago – a veritable mailstorm. I was lucky, I only got 14000 or so messages from one SQL Server over the course of a single long weekend. This was generated by one of the standard SQL Server alerts that had been set up by a predecessor; however, he had failed to make the appropriate adjustments to ensure a sanity-saving delay.
Want to make sure the same doesn’t happen to your inbox? Here’s how, using SSMS. In the Object Explorer, navigate through SQL Server Agent, Alerts, and then select the Alert you wish to configure. Right-click, “Properties”, and select “Options” from the list on the left.
As you can see, there are options to set the delay between alert messages. The job shown here has these values set to zero – which means no delay, so you’ll get stormed. Nice.
This dialog box provides a shell around the msdb.dbo.sp_update_alert system stored procedure. As you can see from the documentation, the only required parameter is the alert name; the rest are optional. Check the @delay_between_responses parameter.
If you want to automate the process and hit every job, then you could do worse than check the msdb.dbo.sysalerts table. I’ll leave this to you to figure out, as it’s late, and I need a beer…