Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    14

    Unanswered: Maintenance Plan/SSAgent - Notifications send anyway... (even when they shouldn't)

    Hello,

    [Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) - running on Windows 2008 enterprise server]

    I have a nightly and weekly job that backs up transaction logs/db's, and send me an email if there is a problem/if the job fails.

    The problem is that I keep getting notifications that the job 'succeeded' even though I have in the properties for the job (in SS Agent\job instance\properties\Notifications\Email... (me) "when job fails") to only send the email to the only operator when the job fails...

    I even looked in the msdb's sysjobs system table and for the job instances it shows 0 (zero) in the notify_level_email. I've tried stopping/restarting the SS Agent after making the changes, but no joy.

    Can anyone shed any light on what I keep getting the notifications?

    Thanks!

    -Matt G.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Script the job, scrub identifiable data, and post the script here.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2009
    Posts
    14
    ...sorry for the delayed response.

    Ok, I looked, and am a bit embarrassed to say I can't figure out how to script out the maintenance plan. Might you be able to assist? (in that as well... geez, I feel like such a newb...)

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Expand SQL Agent in Object Browser of SSMS, expand Jobs, right-mouse-click on the job you need, and locate something like "Script job" or similar to it (don't have a server to connect to handy at this time).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2009
    Posts
    14
    ugh... I was looking to script from the "maintenance plan" area, and not the 'jobs' area... Thanks! (duh)

    Code:
    USE [msdb]
    GO
    
    /****** Object:  Job [MaintenancePlan_weekly]    Script Date: 07/02/2010 14:01:11 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object:  JobCategory [Database Maintenance]    Script Date: 07/02/2010 14:01:11 ******/
    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'MaintenancePlan_weekly', 
    		@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'my_domain\administrator', 
    		@notify_email_operator_name=N'Fname Lname', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** Object:  Step [Subplan_1]    Script Date: 07/02/2010 14:01:11 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Subplan_1', 
    		@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\MaintenancePlan_weekly" /set "\Package\Subplan_1.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'MaintenancePlan_weekly.Subplan_1', 
    		@enabled=1, 
    		@freq_type=8, 
    		@freq_interval=1, 
    		@freq_subday_type=1, 
    		@freq_subday_interval=0, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=1, 
    		@active_start_date=20100315, 
    		@active_end_date=99991231, 
    		@active_start_time=10000, 
    		@active_end_time=235959, 
    		@schedule_uid=N'28f5029c-5501-4d01-a2ff-f26fe5c2067e'
    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
    I would have thought line 20 being set to 2 would make this only on failure... I tested dumping out when set to completes (3) and succeeds (1) - and I saw the select * from sys.database_mirroring showing the value being updated.

    Not sure what else to try... maybe now that I know how to dump it out, i should just delete, reboot and recreate?

    Thoughts? Something I am overlooking here?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Without seeing the Maintenance Plan itself, I can't be certain, but here's what you can do. Right-mouse-click on the maintenance plan in question, and select execution history. Review the entries there. If that is not enough, - disable the job schedule, and run the plan manually. What I suspect is that one of the steps within the plan is failing, which bubbles up to the calling job (therefore - "on failure" gets triggered). But that step is not the last one, while the last one actually succeeds. Remember, this is just a huntch, so don't think of it too much, just check the plan history and maybe run it manually if the history doesn't show errors.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jul 2009
    Posts
    14
    While not very scientific I dropped the maintenance plans and jobs and readded and they work as I expected now.

    Two things. One... I chose *not* to enable (in the maintenance plan wizard) the "email me" results check box - choosing to rather enable the notification to operator area in the job's properties.

    Also, I wonder if this was related to that check box which actually set up sending not of the alert that the job succeeded but rather the sending of the log of the job having run, as per this link: SQL 2005 - Unwanted email notification when Maintenance Plan executes

    ...I would need to test further, but I wonder if that last step to send email in the maintenance plan wizard is sending the log file (which the above link taught me to look into) ... so I perhaps wasn't receiving results of the job per se, but rather a LOG of the results of the job.


    I'm pleased to have learned about where to script out the jobs - which will be useful for managing a generalized set of jobs for the various servers that I am administering.

    Thanks for the help!

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's the reason why I still hate maintenance plans. You can bury so much in so many places, so that your oncall guy will go nuts at 3AM trying to figure out what failed and where.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •