You will need to specify a success and failure action for each job step. You should always create a job step specifically for handling failures. This allows you to customize the actions for each failure. One things that should not be overlooked is the retry interval. You will always use this when importing text files into SQL Server. This allows your job flexibility in case the file transfers are late. You can optionally send the output of the job step to a file. If you do this, try to send all output from a job to a single file. Sending output to a separate file for each job step creates a management nightmare very quickly in any environment. You can optionally append output to the step history. You should not enable this can easily and quickly create a very large history file. Finally, you can run a job step under a specific user context in order to limit the permissions of that step.

Creating a schedule

The steps to creating a schedule are as follows:

1. Select the Schedules tab as shown in Figure 13.11.
2. Specify a name and the type of schedule.
3. If it is a recurring schedule, click the change button and modify the recurrence interval as shown in Figure 13.12.

Creating a schedule for the job.

Two of the most interesting options for a schedule are at SQL Server Agent startup and on CPU idle. On SQL Server Agent start up gives you the ability to run initialization and cleanup jobs whenever the SQL Server Agent starts. Since you will normally start this service every time SQL Server starts, it also gives you the ability to run jobs on the start up of SQL Server. One of the most common applications of this is with applying trace flags. The CPU idle schedule is dictated by the configuration settings we applied earlier in this chapter. This gives you the ability to more efficiently use the processing capability on your SQL Server by running jobs during idle times on the server. You should not execute long running jobs during this time period since a server can go from idle to fully utilized at any time. For shops that do not run 24x7, this gives you a very good opportunity to run jobs as soon as possible instead of picking an arbitrary time during the evening when you think the server might be idle. If you set up a schedule for CPU idle, you should also make sure to set up a secondary schedule to ensure the job runs at least once during a regularly scheduled interval. This is simply because it is entirely possible that your server may never hit an idle condition, but you still need to ensure a job runs regularly. One very good example of this is when utilizing the SQL Server Agent as a report server to e-mail query requests back to users.

SQL Server Agent 1 2 3 4 5 6 7 8 9