I have a SQL Agent job that runs on the 7th of each month on an hourly basis. I want to stop it from running further once it is successful.

For example, if the job is successful at 8:00 A.M, I dont want it to run any more untill the 7th of next month. Any scripts would be helpful for this.

I am working on trying to establish this rule through the use of MSDB sys.jobs and an idea that I have is to update the Enabled flag to a 0 once the run is complete. Once the 7th of next month hits, another job on the SQL Agent could go in an update the flag back to a 1 so it can be run. i

CodePudding user response：

I suggest you create a new first job-step and, using the answer provided in the question sql-agent-job-last-run-status, you can check if the last execution succeeded.

You can then cancel the job from executing by using exec msdb.dbo.sp_stop_job

I use this method in an Availability group cluster (to check if SQL Agent is running on the primary for jobs the require it) and abort the job if not. It works well.

CodePudding user response：

How about, create a table with a row for last successful run, populate it on a successful run.

Then first thing in the job, check that table, and if the last successful run is since the 7th, terminate.

Otherwise run the rest of the job.