Purpose
The following are
some other cases where SQL Server Agent jobs will be useful.
- Data Warehousing Process
- Full, Differential and Transactional log Back
ups
- Index-maintenace tasks such as rebuild or re-organisation
SQL Agent Job Creation Steps:
To create job you
have to right click on Jobs option
under SQL server Agent and then
choose New Job option.
You will get New Job window as follows:
On left pane general,
Steps, Schedules and so on are available for job properties setting.
General:
You fill Job name,
Owner (which user has to run the job), description for the job (explain the
purpose of job and schedule timings). Make sure that you enabled the “Enabled”
Check box.
Steps:
One job can have
multiple steps and you can create multiple job steps and also control the flow
of steps by making use of On Success
and On Failure options in the Advanced Tab.
For example,
- Create job step by clicking on New option
- Fill Step name, Database name, Command as
shown below. Note that mention Path details as C:\Folder\Filename.bak
- Parse can be used to check
for the command syntax errors
- Click OK
for job step creation
Advanced
tab
is available for handling control flow of job steps (if we have more than one
job steps). And you can set up On
Success and On Failure
navigation/notification.
On
Success: this
is useful when we have multiple steps. For suppose we have execution of 2nd
step depend on first step successful run. At that time set “Go to next step
option” for “On success”.
The following are the
options available for On Success:
- Go to next Step
- Go to specific step ID
- Quit the job reporting success
- Quit the job reporting failure
Retry
Attempts: Sometimes
we used to retry the execution in case of failure. We can specify number of
times it should retry job step for success.
Retry
Interval (minutes): You can specify the waiting time for next
retry.
On
Failure:
This is mainly used for failure notification. If you want to notify some of
users for failure, create one more step using msdb..sp_send_dbmail and execute
this step once any of the previous job steps fails.
Usually, this feature
will be set up for “Quit the job reporting failure”. The following are the
options available for On Failure:
- Go to next Step
- Go to specific step ID
- Quit the job reporting success
- Quit the job
reporting failure
Before click on OK you have to create schedule
timings for the job run. Following screen describes the schedule creation. You
can schedule the job daily, weekly, monthly, hourly and so on.
Conclusion
The above are steps
for creating simple job using SQL Server Agent.