Time Periods for Email Notifications

Question

Time Periods for Email Notifications

USKiwi

SSChasing Mays

Points: 604
More actions
January 25, 2011 at 5:47 pm

#244192

Greetings,
I have a situation where alerts are being sent to an email address. These in turn are being sent to a Blackberry device. The problem is that we need to control the time when these alerts are sent. Obviously if SQL crashes because the log ran out of space then you would want to be notified of that at 3AM. On the other hand you don’t need notification at that time of the morning that a backup job failed. However you do need such a notification at 9AM the next morning.
In other words we want to set up black out time periods where these alerts will not be sent. However we also want these alerts to be sent at the next open time period.
There does not appear to be any way to select time periods as to when email notifications are sent to operators. Such functionality does exist with pager notifications where you can select the time periods where the operator is available to receive pager notifications. Does anyone know how SQL 2K5 and above handles alerts that are sent to a pager during these black out periods? Are they simply deleted or are they sent at the next open time period?
I have not yet had a chance to test this and I was hoping that someone would know the answer off the top of their heads. Or whether there is a better way to achieve this functionality?
Thanks in advance.
USKiwi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

IIRC, pages are simply not sent outside the stated hours. A good place to check this out would Books Online, the SQL Server Help System.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Also, from SQL Server 2008 BOL:

Important:

The Pager and net send options will be removed from SQL Server Agent in a future version of Microsoft SQL Server. Avoid using these features in new development work, and plan to modify applications that currently use these features.

USKiwi SSChasing Mays Points: 604 More actions · Answer 3

Hi Lynn:

Thanks for the response.

Putting aside the fact that Paging and Net Send are scheduled to be depreciated and shouldn’t be used for any new development, I still have an interesting problem on my hands. I can use email notification but have no way of filtering out the non-critical alerts that need attention but not an awaking in the middle of the night. I can use paging notification to perform this filtering but if the alert occurs during a pager black out time period it gets lost. Net send will dump the notification to the screen and appears to be the best solution but can still be missed. It seems that the only solution would be to dump these alert messages to a database and have some TSQL logic that determines the importance of the alert and responds accordingly. I’m sure that there is a product out there that does that already. I was hoping that that this functionality was available within SQL Server.

Thanks.

USKiwi

shaun.stuart SSCertifiable Points: 7035 More actions · Answer 4

Hmm..i think this could be done, but it wouldn't be pretty. You can configure alerts for error level 17 through 25. As the Response for those, have them execute a job. You can put your conditional processing in the job and let it determine whether or not to send an email. (Actually, this are all fairly critical errors, so you might just want to always have these send an email. In that case, you don't need a job and just have the response be send an email.)

For jobs, find the ones whose failures are non-critical. Change each step of the job to go to a new step on failure. Have that step do your conditional processing to determine when to send an email. You could also log to a table here as well. Perhaps you could even have another hob that, once the appropriate time rolls around, iterates through your error log table and sends the emails then.

It's not pretty and would require a fair bit of programming, but it could be done.. Maintaining it would an issue too.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Another alternative is to remove all the alerts from your jobs, then create a new job to run every 15 minutes to check for jobs that failed the last time they ran. This job would check against a table of jobs that you created, to find out whether to send an alert now, or whether to wait until the time you specified in the table. If you didn't want to create a seperate table, then you could use job categories to keep the same information.

Hope that makes sense

John