70-431 Microsoft SQL Server 2005 - Implementation and Maintenance Exam Questions

Demo Question 9.

You work as a database administrator at EliteCertify.com. The EliteCertify.com network consists of a single Active Directory domain named EliteCertify.com. Your duties include maintaining scheduled jobs for the SQL Server 2005 databases in your department. You establish that one of the jobs aggregates data from multiple sources for reports. This job runs daily and consists of numerous steps. Every step aggregates data for a specific report. As administrator you have received several complaints from users that the data for a number of reports has not been updated recently. You need to make sure that every step of the job that aggregates data performs even when errors occur. What should you do?

A. You need to change the On Failure action in order to go to the next step.
B. The job needs to be configured to retry the step.
C. You need to create notification alerts. As soon as an error occurs he can rectify the error and restart the job.
D. You need to combine all the steps into a single step that runs daily.


Display Answer

Answer: A

Explanation: When creating Microsoft SQL Server Agent jobs, Andy Booth should 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: 1. 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. 2. 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. 3. 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 the Run as user list is available, select the proxy account with the credentials that the job will use.