Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

E-Mail notifications occurring on success despite configuration in job Expand / Collapse
Author
Message
Posted Friday, February 14, 2014 2:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 6:36 AM
Points: 76, Visits: 168
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.
Post #1541786
Posted Saturday, February 15, 2014 8:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 3,931, Visits: 7,160
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"
Post #1541856
Posted Tuesday, February 18, 2014 11:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 6:36 AM
Points: 76, Visits: 168
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
Post #1542690
Posted Tuesday, February 18, 2014 11:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 3,931, Visits: 7,160
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"
Post #1542692
Posted Wednesday, February 19, 2014 8:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 6:36 AM
Points: 76, Visits: 168
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?
Post #1543033
Posted Wednesday, February 19, 2014 8:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 3,931, Visits: 7,160
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"
Post #1543044
Posted Wednesday, February 19, 2014 10:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 6:36 AM
Points: 76, Visits: 168
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.
Post #1543125
Posted Wednesday, February 19, 2014 10:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 3,931, Visits: 7,160
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"
Post #1543129
Posted Wednesday, February 19, 2014 11:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 6:36 AM
Points: 76, Visits: 168
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.
Post #1543147
Posted Wednesday, February 19, 2014 2:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 3,931, Visits: 7,160
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"
Post #1543225
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse