SQL Server Agent Jobs: How to execute a job step without executing the entire job

Question

I have a SQL Server Agent Job that previously had two steps. Today, I've had to integrate the third step, and soon I'll need to integrate a fourth.

I want to be sure that the step will execute properly but I do not want to execute the entire job.

The first two steps take quite a bit of time to execute and during the day they hog a significant amount of the SQL resources that my users need.

Is there a way that I can execute a job step and not an entire job process?

Within SSMS, if I right-click on the job there is an option that states "Start Job at step..." except when I try that option it brings up a dialog that seems to imply that the entire job has been started. What can I do to test one step within a job?

Thanks in advance.

Answer 1

--how to run only a step of a job

basically I save the current situation of the step within a job - particularly what to do when success and what to do if fails.

then I change it to end the job in success or failure accordingly.

here is the script:

USE [msdb]
GO

declare @on_success_action int,
        @on_fail_action int,
        @run_the_job bit = 0

if object_id('tempdb..#the_only_step',N'U') is not null
   drop table #the_only_step

create table #the_only_step(
step_id int,--  Unique identifier for the step.
step_name   sysname,--  Name of the step in the job.
subsystem   nvarchar(40),-- Subsystem in which to execute the step command.
command nvarchar(max),--    Command executed in the step.
flags   int,--  A bitmask of values that control step behavior.
cmdexec_success_code    int,--  For a CmdExec step, this is the process exit code of a successful command.
on_success_action   tinyint,--  Action to take if the step succeeds:

--1 = Quit the job reporting success.
--2 = Quit the job reporting failure.
--3 = Go to the next step.
--4 = Go to step.

on_success_step_id  int,--  If on_success_action is 4, this indicates the next step to execute.
on_fail_action  tinyint,--  What to do if the step fails. Values are same as on_success_action.
on_fail_step_id int,--  If on_fail_action is 4, this indicates the next step to execute.
server  varchar(100) null,--    Reserved.
database_name   varchar(100) null,--    For a Transact-SQL step, this is the database in which the command executes.
database_user_name  varchar(100) null,--    For a Transact-SQL step, this is the database user context in which the command executes.
retry_attempts  int,--  Maximum number of times the command should be retried (if it is unsuccessful).
retry_interval  int,--  Interval (in minutes) for any retry attempts.
os_run_priority int,--  Reserved.
output_file_name    nvarchar(200),--    File to which command output should be written (Transact-SQL, CmdExec, and PowerShell steps only).
last_run_outcome    int,--  Outcome of the step the last time it ran:

--0 = Failed
--1 = Succeeded
--2 = Retry
--3 = Canceled
--5 = Unknown
last_run_duration   int,--  Duration (hhmmss) of the step the last time it ran.
last_run_retries    int,--  Number of times the command was retried the last time the step ran.
last_run_date   int,--  Date the step last started execution.
last_run_time   int,--  Time the step last started execution.
proxy_id    int
)

insert into #the_only_step
EXEC msdb.dbo.sp_help_jobstep 
        @job_name=N'SUPT_munichRE - after restore', 
        @step_id=2


--you can check if things are there as expected
select * from #the_only_step


--we want to change the step:
--after finished - success and after finished - failure - either way end the job




select @on_success_action= a.on_success_action,
       @on_fail_action = a.on_fail_action
from #the_only_step a

EXEC msdb.dbo.sp_update_jobstep 
        @job_name=N'SUPT_munichRE - after restore', 
        @step_id=2 , 
        @on_success_action=1, 
        @on_fail_action=2

if (@run_the_job = 1) 
   BEGIN

                EXEC msdb.dbo.sp_start_job 
                @job_name=N'SUPT_munichRE - after restore',
                @step_name = N'2'

                PRINT 'job started'

   END
 
 --after it finishes running
  --set the values back as they originally were


  EXEC msdb.dbo.sp_update_jobstep 
        @job_name=N'SUPT_munichRE - after restore', 
        @step_id=2 , 
        @on_success_action=@on_success_action, 
        @on_fail_action=@on_fail_action

Now another interesting point is

after starting the job, how to wait until it is finished before moving on?

that is answered here:

--[is there a way to run the same job multiple times without scheduling it?][1]

--An INSERT EXEC statement cannot be nested. --http://stackoverflow.com/questions/3795263/errors-insert-exec-statement-cannot-be-nested-and-cannot-use-the-rollback-s

Basically I use that stored procedure to run the job instead of msdb.dbo.sp_start_job as in the script above.

hope this helps as it is very useful to me!

Answer 2

USE [**DB_NAME**]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[**STEP_NAME**]  

SELECT 'Return Value' = @return_value
GO

Answer 3

In SSMS:

• Copy the code from the job step (use ctrl + a to select all)
• Paste the code in to a new query window in SSMS
• Run the code

Alternately if you have a recurring need to run just a couple steps.

Put those steps in a separate job, then kick off one job from the other to run everything. Use EXEC msdb.dbo.sp_start_job N'job_name'

You can run the short easy job on demand, and the full long job as scheduled

Answer 4

Use this:

EXEC msdb.dbo.sp_start_job N'job_name' , 'step_name'

Answer 5

Go to job properties and manually do that step. For example, there is a job with 16 steps and you want to run the 12th step, then go to that job property and see what that step 12 exactly does. If it executes a batch file in command prompt, do that manually. But this logic cannot be used in all cases.

Answer 6

"Start job at step" will start the job at the step you specify. However - if you don't wish to execute any subsequent steps - be sure to adjust the step logic so that it will "Quit reporting success" after completing the step you started at.