sql-serversql-server-agentsql-agent-job

Disable SQL Server Agent job on failure

I have a daily scheduled SQL job on SSMS with multiple steps, if any of these steps fail, I want to disable the job in order to prevent the scheduled job from running the following day.

I have currently got this working by setting step 1 to check the value in a control table and only continue if the value is 1. Then on failure of any following steps, jump to step 10 which is a T-SQL script that updates the value in this table to 0

Whilst this does work, I think disabling the job entirely seems cleaner. Is there a way to do this?

Solution

  • In your step after the job failes you can run this to disable the job:

    EXEC dbo.sp_update_job  
    @job_name = 'JobNameHere',  
    @enabled = 0 ;

    Then you can run a send mail piece to send an email that it failed.

     EXEC msdb.dbo.sp_send_dbmail
              @recipients = '',
              @subject = '',
              @body = ''