February 14, 2014 at 2:55 pm
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.
February 15, 2014 at 8:54 am
Please post your TSQL generating the alerts...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 18, 2014 at 11:13 am
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
February 18, 2014 at 11:23 am
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
February 19, 2014 at 8:00 am
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?
February 19, 2014 at 8:10 am
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
February 19, 2014 at 10:24 am
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.
February 19, 2014 at 10:32 am
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
February 19, 2014 at 11:18 am
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.
February 19, 2014 at 2:26 pm
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
February 19, 2014 at 6:04 pm
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
February 19, 2014 at 7:30 pm
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
February 20, 2014 at 7:19 am
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
February 20, 2014 at 7:28 am
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
February 20, 2014 at 7:52 am
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