7

I have a project coming this weekend where all the jobs has to be stopped but only 1 step in a particular job has to be run. for eg: i have a a 2 step job and the second step must run and the first step has to be disabled or stopped along with the other jobs.

First of all i thought of stopping the sql server agent, but that will stop all the jobs. Is there any way to do it.

3 Answers 3

7

You can't disable a particular job step.

You have two options, the way I see it:

  1. Change the job to have the "start step" as your second step, and have the second step's "on success action" to quit the job (if there are subsequent steps)

  2. Create a separate job with the step's logic and then only use the new job that does the work

2
  • I agree with Thomas. He illustrates two very simple means to achieve the result of skipping the steps as requested.
    – SQLRNNR
    Commented Sep 24, 2014 at 13:46
  • so, i created a separate job with the steps and will be manually running it. How can i stop the rest of the job other than going manually to each one of them and stopping it. Because the jobs are run on different times, i cannot manually stop it unless it is running. I get an error message when i try to stop the jobs which are not running. error : Stop failed for Job "job name" Error :22022. Request to stop job refused because the job is not currently running. How can i over come that and stop the rest of the jobs and run just 2 ?
    – user48062
    Commented Sep 25, 2014 at 13:31
1

Another option, if you expect to be "playing" around with the jobs order very often, you can follow the following steps. We use it extensively and it works flawlessly.

  1. create a dummy step that just
    • let it run tsql code like: WAITFOR DELAY '00:00:01'
    • select Quit the job reporting success behavior for On success action on Advanced tab of step configuration
  2. place that step where you need to stop job execution
  3. save the job, it will detect that some of the steps will not be executed and warn you about, select yes and you're done.

Here is a picture to illustrate it. Dummy step is on position 8 so step on 9 will not be executed.

Show steps on job

5
  • Question is about skipping first step. Your answer is about skipping last step. Commented Sep 24, 2014 at 15:22
  • Question was about not running one particular step of a job and as example the OP mentioned not running 1st one. Is as easy as taking that one and changing the order to, say 9, on my example. Then which used to be 2nd, now is the first...easy peasy. With my answer you can leave out of the job as many steps you want without the need of creating more jobs, deleting steps or whatever.
    – Yaroslav
    Commented Sep 24, 2014 at 17:15
  • 1
    Awesome. Thank you very much. Appreciate all your help.
    – user48062
    Commented Sep 25, 2014 at 14:08
  • Another question is how to stop the rest of the jobs ?
    – user48062
    Commented Sep 25, 2014 at 14:14
  • You are now talking about a different thing, stopping jobs, not preventing steps inside of a job to execute. My advice, choose from the answers the one that solves your current question, check it as correct answer. Then create a new question with the new scenario, maybe using the comment you added on the other answer.
    – Yaroslav
    Commented Sep 25, 2014 at 14:16
0

I know this is an ancient post and I hope you have found a suitable solution for you :)

For me, the proposed solutions were not good (we have lots of jobs spanning multiple databases on the same server instance, and while we migrated one DB elsewhere, I wanted to turn off just the steps related to this DB...)

Adding my solution here, so it could be found by future generations :) basically, I just commented out the whole command, and to make it work, I had to first make the original comments somehow different

begin tran update msdb.dbo.sysjobsteps set command=
'/*DISABLED EVERYTHING FOR THIS DB '+replace(replace(command, '/*', '/DISABLED\*' ), '*/', '*\DISABLED/')+' DISABLED EVERYTHING FOR THIS DB*/' 
from msdb.dbo.sysjobsteps 
where database_name in (
'mydb1',
'mydb2')

To reenable the steps, just replace it back

replace(replace(replace(REPLACE(command, '/*DISABLED EVERYTHING FOR THIS DB ',''), ' DISABLED EVERYTHING FOR THIS DB*/', ''), '/DISABLED\*', '/*'), '*\DISABLED/', '*/')

Unless your commands contain very weird substrings, this should not break anything (i chose the inner replacements so that they are easily recognized and were not in my DB anywhere).

Of course, you can tweak your WHERE clause depending on what you want to disable/enable :)

Edit: by the way, you would also want to set on_fail_action=3 (go to next step) in case you take that that particular database offline (because the Agent tries to connect to the database anyway even it the step contains nothing, and it fails, and it would quit the job)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.