E-Mail notifications occurring on success despite configuration in job

  • 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.

  • Please post your TSQL generating the alerts...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • 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

  • 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; Theyll drag you down to their level and beat you with experience

  • 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?

  • 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; Theyll drag you down to their level and beat you with experience

  • 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.

  • 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; Theyll drag you down to their level and beat you with experience

  • 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.

  • 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; Theyll drag you down to their level and beat you with experience

  • Hello --

    I went to the job in question, and in the Properties window, I went to Notifications. The settings are the following:

    E-Mail <Operator> When job fails

  • Script out the jobs you are having issues with and post them here. There has to be some TSQL script sending the email...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Per your request, I am posting the T-SQL scripts for the job in question:

    Taken from the Maintenance Plan -> Backups -> Subplan_3

    BACKUP LOG [RayStationClinicDB_25189] TO DISK = N'T:\BACKUP_25\RayStationClinicDB_25189_backup_2014_02_20_091754_5984039.trn' WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N'RayStationClinicDB_25189_backup_2014_02_20_091754_5984039', SKIP, REWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP LOG [RayStationMachineDB_25189] TO DISK = N'T:\BACKUP_25\RayStationMachineDB_25189_backup_2014_02_20_091754_6044045.trn' WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N'RayStationMachineDB_25189_backup_2014_02_20_091754_6044045', SKIP, REWIND, NOUNLOAD, STATS = 10

    GO

    BACKUP LOG [RayStationPatientDB_25189] TO DISK = N'T:\BACKUP_25\RayStationPatientDB_25189_backup_2014_02_20_091754_6094050.trn' WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N'RayStationPatientDB_25189_backup_2014_02_20_091754_6094050', SKIP, REWIND, NOUNLOAD, STATS = 10

    Taken from the Job Step Properties under the SQL Agent -> Jobs -> Transaction Logs Backups

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

  • This isn't what I meant. This is the maintenance plan's "auto-generated" code.

    Try opening SSMS, on the server this all runs on expand the (+) for the SQL Server Agent, expand the (+) for Jobs, find your job in the list, then right-click, choose "Script Job as ->", then choose "Create to ->", then "New Query Editor Window".

    This will give you a script for the actual job (which will include all the TSQL for everything the jobs does, for example:USE [msdb]

    GO

    /****** Object: Job [DBA - Check for Long Running Jobs] Script Date: 02/20/2014 08:26:40 ******/

    BEGIN TRANSACTION

    D ECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [Database Maintenance] Script Date: 02/20/2014 08:26:40 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA - Check for Long Running Jobs',

    @enabled=1,

    @notify_level_eventlog=2,

    @notify_level_email=2,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'01/14/2011 - MDJ - Created to be used by DBA''s to monitor whatever is needed i.e. (failed jobs, long running jobs, general maintenance, etx)',

    @category_name=N'Database Maintenance',

    @owner_login_name=N'sa',

    @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [Check for Long Running Jobs] Script Date: 02/20/2014 08:26:40 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check for Long Running Jobs',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'/* Last parameter denites time in minutes that job will check for */

    exec utl_dba_CheckLongRunningAgentJobs 1, ''mydoggiejessie@company.com'', 300',

    @database_name=N'F1Settings',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Hourly',

    @enabled=1,

    @freq_type=4,

    @freq_interval=1,

    @freq_subday_type=8,

    @freq_subday_interval=1,

    @freq_relative_interval=0,

    @freq_recurrence_factor=0,

    @active_start_date=20110114,

    @active_end_date=99991231,

    @active_start_time=0,

    @active_end_time=235959,

    @schedule_uid=N'0129162e-527e-4a58-a6be-50e5e4871b8d'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    GO

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Let's try this again:

    USE [msdb]

    GO

    /****** Object: Job [RayStation 25 Transaction Log Backup] Script Date: 02/20/2014 09:50:30 ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [Database Maintenance] Script Date: 02/20/2014 09:50:31 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'RayStation 25 Transaction Log Backup',

    @enabled=1,

    @notify_level_eventlog=2,

    @notify_level_email=2,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'No description available.',

    @category_name=N'Database Maintenance',

    @owner_login_name=N'sa',

    @notify_email_operator_name=N'RODB Admins', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [Subplan_3] Script Date: 02/20/2014 09:50:31 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Subplan_3',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'SSIS',

    @command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\RayStation 25 Backups" /set "\Package\Subplan_3.Disable;false"',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RayStation 25 Backups.Subplan_3',

    @enabled=1,

    @freq_type=4,

    @freq_interval=1,

    @freq_subday_type=8,

    @freq_subday_interval=1,

    @freq_relative_interval=0,

    @freq_recurrence_factor=0,

    @active_start_date=20130207,

    @active_end_date=99991231,

    @active_start_time=0,

    @active_end_time=235959,

    @schedule_uid=N'58caf469-8f71-44af-b9a8-9dca11cc85d4'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    GO

Viewing 15 posts - 1 through 15 (of 18 total)

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