Microsoft SQL Server Front End Blog

The writers of this blog are a part of the Microsoft SQL Server Manageability team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Agent, Data collector and other tools

Creating multi steps SQL Agent job

Creating multi steps SQL Agent job

  • Comments 1

I will explain how to add another step to existing job and configure it correctly.

Let's assume that you already have a job with 1 step (for example as in Sethu's backup job example)

1. Open SSMS (SQL Server Management Studio, make sure that SQL Agent is running) navigate to Jobs folder under SQL Server Agent and select desired job. In my Exmple it is Demo DB Backup Job, right click on properties to edit the job.

In opened dialog (see below image) select job steps (it will show your current active step) and click on New button to add another step.

 

2. In new step dailog do the following (see below)

  •  Provide a new step name,
  •  Select step execution type (in this exmaple it is T-SQL).
  •  Select Database - against which you will script. (if you are scriptiong against multiple databases you can leave it as default (Master) just make sure that you are using full qualified names in your script).
  •  Provide T-SQL script to execute, in this example I'm doing table cleaning (based on the date)

 

3. Configure advance options of the step (see image below)

  • Change success action to Quit the job reporting success (by defualt it is set to Go to next step)
  • You may specify number of retries if you wish as well. It is a number of retiries that system will try to execute this step in case of failure. You can also specify retry intervals (in minutes) between each retry.
  • Specify your failure action - In this pecific example I set it to Quite the job reporting failure (the deafult)
  • The output of the step can be stored in the file/table or ainclude it in the job history. In this example I set to store output of the step into the table and add it to the history.
  • Click OK to save the step

Note:  The system doesn't control the size of the table storing the job steps output. It is a job owner responsibility to clean this table via sp_delete_jobsteplog stored procedure.

Note: The size of the History on the other hand is configurable, here how to do it in SSMS: Right click on the SQL Server Agent node then navigate to properties->History. By default Maximum job history log size (in rows) is 1000. Maximum job history rows per job is 100.

 

 

4. The next step will be to adjust the previous first step on success to execute the next step.

Note: The previous step will not be adjusted automatically to move to next step!

  • Select Steps page and navigate to the previous Step (step 1 in this example). Click edit to modify the step
  • Navigate to Advanced option and change on success action to be Go to next Step.
  • Click Ok to save the data

5. You are done! Now you can execute your new multi steps job. During the execution you will see the following difference: The system will recognize that your job is a multi steps job and will offer you the option to execute from specific step (meaning that you can start job execution from any step)

 

6. After execution, in the history page, you can see the results per each step:

 

 

This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
  • when i automate this to happen on a specific time.It does not start at all do i have to give some parameters ??

Page 1 of 1 (1 items)