Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


E-Mail notifications occurring on success despite configuration in job


E-Mail notifications occurring on success despite configuration in job

Author
Message
kaplan71
kaplan71
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 324
Hello --

We are running SQL Server 2008R2 and have several scheduled jobs as part of an overall Maintenance Plan. One of the jobs is the Transaction Log backup. The job in question is configured to send e-mail notifications on failure. During a recent configuration of the server the public profile for the e-mail account for all jobs was set to default, and shortly thereafter, the notifications of successful transaction log backups began to appear.

While this is not a major issue, several administrators have requested that only failure notifications for transaction log backups be sent. I checked the configuration of the job, and it is configured to do just that.

Why would success notifications be sent out, and how can the notifications be reconfigured so that only backup failures for the transaction logs get sent by e-mail?

Thanks.
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4911 Visits: 7365
Please post your TSQL generating the alerts...

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
kaplan71
kaplan71
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 324
Hello --

Thank-you for your reply. As you requested, the T-SQL code for the Transaction Log Backups are listed below:

BACKUP LOG [RayStationClinicDB_25189] TO DISK = N'T:\BACKUP_25\RayStationClinicDB_25189_backup_2014_02_18_130805_7401648.trn' WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N'RayStationClinicDB_25189_backup_2014_02_18_130805_7391647', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [RayStationMachineDB_25189] TO DISK = N'T:\BACKUP_25\RayStationMachineDB_25189_backup_2014_02_18_130805_7551663.trn' WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N'RayStationMachineDB_25189_backup_2014_02_18_130805_7551663', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [RayStationPatientDB_25189] TO DISK = N'T:\BACKUP_25\RayStationPatientDB_25189_backup_2014_02_18_130805_7621670.trn' WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N'RayStationPatientDB_25189_backup_2014_02_18_130805_7621670', SKIP, REWIND, NOUNLOAD, STATS = 10


The command line for the job itself is the following:

/SQL "Maintenance Plans\RayStation 25 Backups" /SERVER "MGHROSTORAGE2\RAYCLINICAL" /CHECKPOINTING OFF /SET "\Package\Subplan_4.Disable";false /REPORTING E

MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4911 Visits: 7365
OK, is it safe for me to assume that you are using the Maintenance Plan Wizards and you have a connection from the actual Maintenance task ---> the Notify Operator task?

If this is the case, can you confirm that in the Precedence Constraint Editor, that you have the Evaluation Operation set to "Constraint", Value set to "Failure", and "Logical And. A;; constraints must evaluate to True" checked?

Or is this a custom maintenance plan? If so, please provide the actual TSQL code you are using to generate the email on failure

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
kaplan71
kaplan71
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 324
Hello --

I am working with the Maintenance Plan Wizard within the SQL Studio application, and I have access to the Maintenance Plan Task -> Notify Operator Task option. I am not clear as to what needs to be done. At the risk of sounding like a complete idiot, could you please provide further information?

Also, would it be better to simply create the maintenance plan from scratch, and make sure the e-mail report option is not activated?
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4911 Visits: 7365
No worries, we are all here to learn :-)

If this is the case, can you confirm that in the Precedence Constraint Editor, that you have the Evaluation Operation set to "Constraint", Value set to "Failure", and "Logical And. A;; constraints must evaluate to True" checked?

1. You need to double-click on the line/arrow from your maintenance task, that joins to your email task. This opens the "Precedence Constraint Editor"
2. In this new pop-up window, set the Evaluation Operation to "Constraint"
3. Set Value to "Failure"
4. In the last choice at the bottom, set the "Logical And. A;; constraints must evaluate to "True"

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
kaplan71
kaplan71
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 324
The "work area" of the Studio has only one task box. This is the actual backup of the transaction logs. There is no additional box in the area referencing the e-mail notifications. It would seem that I would need to add the Notify Operator task box from the Maintenance Plan task list.

I did further investigating into this, and according to a Microsoft TechNet article, there is a bug that prevents modification of an existing Maintenance Plan in regards to the saving the report to either a file or sending it via e-mail.
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4911 Visits: 7365
But you've never mentioned sending a report or a file via email, only that people were receiving emails in both cases when the result was good/bad.

So, it sounds like you have a maintenance plan that has a back up log task, and at the SQL Server Agent Job that was created, you've added additional functionality to send the notification? If this assumption is correct, then this is a different issue altogether.

Please confirm

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
kaplan71
kaplan71
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 324
That is correct, and my apologies for the confusion. I have the Maintenance Plan created, and there are jobs under SQL Agent. The Maintenance Plan consists of the Full, Differential, and Transaction Log backups, while the SQL Agent -> Jobs folder contains each of the jobs.
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4911 Visits: 7365
So under the Agent "Notifications" tab, do you have "Email" checked, the appropriate "Operator" selected, and "When the job fails" selected? or are you doing everything via TSQL?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search