You are the database administrator of your company. You are responsible for managing all databases stored on a SQL Server 2008 computer named SQL1.
You create a job with multiple steps that collects data from multipletables for reporting purposes. Each job step collects data for a particular report.
You configure the job to run daily. Users report that data for several reports is not updated regularly. You suspect that all steps configured in the jobare not being performed.
You want to ensure that each job step is performed even when if an error occurs. What should you do?
A.
Modify the On success action option for all the steps in the job.
B.
Modify the On failure action option for all the steps in the job.
C.
Modify the Retry attempts option for all the steps in the job.
D.
Modify the Retry interval (minutes) option for all the steps in the job.
Explanation:
You should modify the On failure action option for all the steps in the job. You can specify an action that SQLServer should take when a job step executes successfully or when a failure occurs during the execution of a jobstep. You can configure the On success action option and the On failure action option on the Advanced pageof the job step to specify the action that SQL Server should take in each situation. You can configure the followingthree actions: Go to the next step , Quit the job reporting failure , or Quit the job reporting success . Bydefault, the Quit the job reporting failure option is selected for each job step if any error occurs during theexecution of the job step. This prevents the remaining steps configured in the job from being performed. Toensure that each job step is performed even when errors occur, you should select the Go to the next step actionfor the On failure action option for each job step. You should not modify the On success action option for all the steps in the job. By default, the Go to the nextstep action is selected for the On success action option. This ensures that when a job step is successfullyperformed, the next job step will be performed. To ensure that all job steps are performed even when erroroccurs, you should select the Go to the next step action for the On failure action option of each job step. You should not modify the Retry attempts option for all the steps in the job. The Retry attempts option specifiesthe number of times a job step should be repeated before it is considered to have failed. When a job is consideredto have failed, the action specified in the On failure action option is taken. By default, the Quit the job reportingfailure option is selected for each job step if any error occurs during the execution of the job step. To ensure thatall job steps are performed even when an erroroccurs, you should select the Go to the next step action for the On failure action option for each job step. You should not modify the Retry interval (minutes) option for all the steps in the job. The Retry interval(minutes) option is used to specify the number of minutes that must pass before a job step is retried. Modifying the Retry interval (minutes) option for all the steps will not ensure that each job step is performed even whenerrors occur.
Objective:
Monitoring and Troubleshooting SQL ServerSub-Objective:
Identify SQL Agent job execution problems.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Administration: How-to Topics (Database Engine) > Automated Administration How-to Topics (SQL ServerManagement Studio) > How to: Set Job Step Success or Failure Flow (SQL Server Management Studio)