Sometimes when your SQL Server Agent jobs fail you don’t want them to run again, It’s a simple extra step to disable the job.
Firstly create a new job step to disable the job, perhaps called DISABLE JOB STEP and put thisT-SQL in the job step command exec msdb..sp_update_job @job_name = ‘YOUR JOB NAME HERE’, @enabled = 0 changing it for your job name as appropriate. This T-SQL will disable the job with that name. Although we can disable a job by the id it is much easier to use and understand if we reference the job by name.
Next you add some glue to make it all work! In the job step that might fail go the the advanced section in this job’s step properties and set the On Failure Action to our disable job step. Leave the success as it is.
Finally if we have notification on failure we actually want our disable job step to report failure on success, yes that’s right report failure on success! so that we would get alerted that disabled job failed.
This helped me today. Thanks!
Excellent, Happy to help!
Two years later, found this on Google. Worked for me, thank you!