Scheduling SQL Jobs with SQL Server Management Studio

Scheduling SQL jobs in the SQL Server Management Studio

Scheduling SQL jobs in the SQL Server Management Studio can be very straight-forward.  First, start SQL Server Management Studio, and log in.

From object explorer tree, expand the "SQL Server Agent", and right click on "Jobs".  Select "New Job".  Enter a name to identify the job, and then select "Steps".

Select "New" to create the first step, and give it a name to identify the specific step.  Select the database to use, and you are ready to setup the SQL commands for the first step.

If you previously setup a SQL command file, then you can select "Open", and open that file.  Otherwise, you can type in the SQL commands that you want to use in the "Command" area.

Once you have setup the SQL commands, select "Advanced".  Here you will determine what happens if the step succeeds, or fails.  If this is the only step, select "Quit the job reporting success" for the "On success action".  Otherwise, select "Go to the next step".

For the "On failure action", the default is "Quit the job reporting failure".  If you can select "Go to the next step" here, if you want to go forward, even though the first step failed.

Once you are satisfied with the first step, select "OK".  

You can add additional step, by selecting "New" on the step screen.  You can also edit, or delete, existing steps by clicking on the step, and selecting "Edit" or "Delete", as appropriate.

Once all of the steps are setup, select "Schedules", to setup when the job is supposed to run.  You can also select "OK" from here, a just run the job manually.

If you are setting up a schedule, then select "New", and name the schedule.  Select the schedule type, such as a one-time, or recurring task.  Select the appropriate entries on the rest of the screen, for how often the job should run, the days, what time, and so forth.

At this point, click "OK" on the "New Job" window, and the job will be created.  It will run based on the schedule that you setup.

If you did not setup a schedule, or want to run a job manually at some other time, expand "Jobs" in the object explorer tree.  Then, right-click on the job name, and select "Start job at step".  If there is only one step, the job will start running.  If there is more than one step, you will have the option of starting at any particular step.

As always, TEST BEFORE YOUR IMPLEMENT.   Run a test job with simple, non-production data that is easy to follow.  Review your results and tune until it is perfected.

Dave

 

Leave a Reply