4

I configured my SQL Server 2014 database to back up to an Azure storage account. I also enabled email notifications via 

EXEC msdb.smart_admin.sp_set_parameter
@parameter_name = 'SSMBackup2WANotificationEmailIds',
@parameter_value = 'd.hilgarth@fire-development.com'

However, this is sending me an email every 15 minutes which tells me that everything is ok. I only want to get an email if there is a problem.

Question: What do I need to change to only get emails in case of an error?

BTW: I have a server that I configured some time ago with the same script that behaves the way I want, so I know it is possible. But because I used the same script as back then, I don't know the difference in the configuration and know of no way to figure it out.

Daniel Hilgarth
  • 75
  • 1
  • 10
  • Can you check and post the output of this: `SELECT * FROM smart_admin.fn_get_current_xevent_settings()` sql query. This should return the current Extended Event settings and current configurations. – Diamond Feb 22 '16 at 20:37
  • It's the same on both servers, the one that is behaving correctly and the one that is not. http://pastebin.com/WnELpEzZ – Daniel Hilgarth Feb 22 '16 at 21:00
  • It looks like as default settings. Do you have access to database mail configuration wizard? Can you see, if you can change the value of logging level to normal as mentioned here: https://msdn.microsoft.com/en-us/library/hh245116%28v=sql.120%29.aspx#SystemParameters – Diamond Feb 22 '16 at 21:22
  • Both databases have "Extended". I am changing it to Normal to see if this helps, but I think that there is something else still going on. – Daniel Hilgarth Feb 22 '16 at 22:56
  • I have also the same feeling, but you might give it a try. For me, It is difficult to comment wihout knowing the setup in details. – Diamond Feb 22 '16 at 23:04
  • @bangal: No, that didn't help. Please let me know which details you need. – Daniel Hilgarth Feb 23 '16 at 14:22
  • Did you ever fix this? I have a similar issue. – Christopher Edwards Jul 10 '17 at 11:45
  • No, never :-( But I did find this: https://msdn.microsoft.com/en-us/library/dn449495(v=sql.120).aspx Maybe it helps you – Daniel Hilgarth Jul 10 '17 at 13:36

2 Answers2

0

It seems like you're doing the right thing, but it is possible that the notifications happening every 15 minutes are coming from a different process.

As you found most likely from this article https://msdn.microsoft.com/en-us/library/dn449488(v=sql.120).aspx

"Enable e-mail notifications to receive backup errors and warnings: From the query window, run the following Transact-SQL statements:"

EXEC msdb.managed_backup.sp_set_parameter
@parameter_name = 'SSMBackup2WANotificationEmailIds',
@parameter_value = '<email1;email2>'

Also on that page is a method for obtaining all of the scheduled notification events:

--  View all events in the current week
Use msdb;
Go
DECLARE @startofweek datetime
DECLARE @endofweek datetime
SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) 
SET @endofweek = DATEADD(Day, 7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)

EXEC smart_admin.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek;

Is it possible that the notification that you're getting every 15 minutes is part of a different scheduled notification and that you're not getting the other notification because nothing is going wrong with the backup?

p_q
  • 16
  • 3
  • Thanks for your answer. It's pretty unlikely that it is a different process, because the email looks like that: http://screencast.com/t/SgXeSAnad And that is the instance I configured the backup for. The output of the script you posted doesn't show any relation to the mails. The timestamps are different from the times the emails are being sent. – Daniel Hilgarth Feb 17 '16 at 11:05
0

The emails are generated by an Agent Job named "smartadmin health check job". So far as I can tell, there is no way to reverse the effect of the command:

EXEC msdb.managed_backup.sp_set_parameter  
    @parameter_name = 'SSMBackup2WANotificationEmailIds', 
    @parameter_value = 'MyEmail@MyDomain.com';

You can't set the @parameter_value to NULL or an empty string or you get this nicely specific and accurate error message:

Msg 45204, Level 17, State 2, Procedure sp_set_parameter, Line 18 [Batch Start Line 0] The parameter @parameter_value cannot be NULL or empty. Provide a valid parameter value.

I also haven't found a way to only have the email generated if there are errors.

Mark Freeman
  • 131
  • 1
  • 3