20

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.

Nitish Gupta
  • 45
  • 7
RLH
  • 15,230
  • 22
  • 98
  • 182

5 Answers5

21

"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.

Rich
  • 3,781
  • 5
  • 34
  • 56
  • Ok, I see how it works now. I had used that method earlier and recalled being a bit confused. Like I had mentioned, my first to steps use up a lot of resources, so I didn't want to experiment. Thanks for the clarification. – RLH Jun 21 '11 at 14:17
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

James Jenkins
  • 1,954
  • 1
  • 24
  • 43
2

Use this:

EXEC msdb.dbo.sp_start_job N'job_name' , 'step_name'
Bugs
  • 4,491
  • 9
  • 32
  • 41
Mehrdad Alemi
  • 119
  • 1
  • 10
  • 6
    That doesn't run just a given step but does the same as going through the GUI it starts the job at a given step and sub-sequentially will follow the job normal flow so unless you are running the last step if you don't change the logic it can run all the steps in a job depending on which step you are starting at. – Jevl Oct 20 '17 at 13:00
  • 4
    Additionally this code is incorrect. The correct code would be Use this: EXEC msdb.dbo.sp_start_job N'job_name' , @step_name = 'step_name' – James Jenkins Dec 03 '19 at 18:16
-1

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.

madhukar mohan
  • 37
  • 7
-1
USE [**DB_NAME**]
GO

DECLARE @return_value int

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

SELECT 'Return Value' = @return_value
GO
Cosi Melchor Nolasco
  • 1
  • 1
  • Hi Cosi, For more effective help from other members regards your question, please edit your question and add more details, as where the code fail to accomplish your needs. Welcome to StackOverflow :) – Ashraf Alshahawy May 27 '21 at 03:00