You are responsible for maintaining scheduled jobs for the SQL Server 2005 databases in your department. One of the jobs aggregates data from multiple sources for reports. This job runs daily. The job consists of multiple steps. Each step aggregates data for a specific report. A user reports that the data for some of the reports has not been updated recently.
You need to ensure that every step of the job that aggregates data executes even when errors occur. What should you do?
A.
Combine all of the steps into a single step that runs once a day.
B.
Create a notification that alerts you when an error occurs so that you can correct the error and restart the job.
C.
Change the On Failure action to go to the next step.
D.
Configure the job to retry the step.
Explanation:
When creating Microsoft SQL Server Agent jobs, you can specify what action SQL Server should take if a failure occurs during job execution.
Determine the action that SQL Server should take upon the success or failure of each job step. Then use the following procedure to configure the job step action flow logic by using SQL Server Agent.
1. In Object Explorer, expand SQL Server Agent, and then expand Jobs.
2. Right-click the job you want to edit, and then click Properties.
3. Select the Steps page, click a step, and then click Edit.
4. In the Job Step Properties dialog box, select the Advanced page.
5. In the On success action list, click the action to perform if the job step completes successfully.
6. In the Retry attempts box, enter the number of times from 0 through 9999 that the job step should be repeated before it is considered to have failed. If you entered a value greater than 0 in the Retry attempts box, enter in the Retry interval (minutes) box the number of minutes from 1 through 9999 that must pass before the job step is retried.
7. In the On failure action list, click the action to perform if the job step fails.
8. If the job is a Transact-SQL script, you can choose from the following options:
*In the Output file box, enter the name of an output file to which the script output will be written. By default the file is overwritten each time the job step executes. If you do not want the output file overwritten, check Append output to existing file.
*Check Log to table if you want to log the job step to a database table. By default the table contents are overwritten each time the job step executes. If you do not want the table contents overwritten, check Append output to existing entry in table. After the job step executes, you can view the contents of this table by clicking View.
*Check Include step output in history if you want the output included in the step’s history.
Output will only be shown if there were no errors. Also, output may be truncated.
9. If theRun as user list is available, select the proxy account with the credentials that the job will use.