You are the database administrator for a major shipping company. You manage all the SQL Server 2008instances of the company.
For one of your instances, you have created jobs to perform regular administrativeactivities.
Some of these jobs in the database fail because the server went down due to a power failure.
You want toanalyze these failed jobs. You also want to find out the tasks performed by these failed jobs.
Which query will you use to achieve the objective?

A.
SELECT job_id, step_id, step_name from msdb.dbo.sysjobhistory
WHERE run_status = 1

B.
SELECT job_id, step_id, step_name from msdb.dbo.sysjobactivity
WHERE run_status = -1

C.
SELECT job_id, step_id, step_name from msdb.dbo.sysjobhistory
WHERE run_status = 0

D.
SELECT job_id, step_id, step_name from msdb.dbo.sysjobactivity
WHERE run_status = 0

Explanation:

The following option is correct:
SELECT job_id, step_id, step_name from msdb.dbo.sysjobhistory
WHERE run_status = 0
The sysjobhistory system table provides a historical record of the jobs that previously executed. This tableresides in the msdb database and contains columns, such as job_id
, step_id , and step_name , which identifyjobs and the steps involved in the jobs. To retrieve the details of tasks being performed by the failed jobs, youshould retrieve rows for only the jobs that failed. You can accomplish this by specifying a condition of run_status=0 in the WHERE clause of the query. A value of 0 in the run_status column indicates a failed job.Therefore, the job_id , step_id , and step_name columns will be retrieved only for the jobs that have failed. You should not use the following query:
SELECT job_id, step_id, step_name FROM msdb.dbo.sysjobhistory
WHERE run_status = 1;
This query uses a condition of run_status=1 in the WHERE clause. A value of 1 in the run_status columnindicates that the jobs completed successfully. In this scenario, you must retrieve details about the jobs that failed,not the ones that successfully completed. You should not use the following query:
SELECT job_id, step_id, step_name FROM msdb.dbo.sysjobactivity
WHERE run_status = -1;
This query uses the sysjobactivity system table, which does not provide information for failed jobs. The sysjobactivity system table provides details on current jobs. This query will generate an error because the sysjobactivity system table does not contain a step_id , step_name , or run_status column. You should not use the following query:
SELECT job_id, step_id, step_name FROM msdb.dbo.sysjobactivity
WHERE run_status = 0;
This query will generate an error because the sysjobactivity system table does not contain a step_id , step_name , or run_status column. The sysjobactivity system table does not provide information for failed jobs.The sysjobactivity system table provides details on current jobs and their job steps. This table containsinformation, such as the time the last step in the job executed and the time at which the job is scheduled to runnext.

Objective:
Maintaining SQL Server Instances

Sub-Objective:
Manage SQL Server Agent jobs.

______________________________________________________________________________________________________________ + QA
(Clear-Page 106 – Screenshot of Solution, but but supose management policy question) Mandate Database Subscription
(Albanian reconstruct of the question, following check options from the first right Answer to the wrong Options:)
How to to easily apply all Policy-Based Management policies in aparticular category to the entire instance of SQL Server 2008, when open the Manage Policy Categories dialog?
select the Mandate Database Subscriptions check box for a category to ensure that all policies in that category are applied to the entire instance.
clear the Mandate Database Subscriptions check box for the CorpPolicies category in the Manage Policy Categories dialog box.
ensure that the Server restriction option for all policies in the CorpPolicies category is set to None.
ensure that the instance of the SQL Server 2008 is specified in the Against targets field for allpolicies in the CorpPolicies.

Explanation (source of Reconstruction of + QA):

The Manage Policy Categories dialog box allows you to easily apply all Policy-Based Management policies in aparticular category to the entire instance of SQL Server 2008.
You should select the Mandate DatabaseSubscriptions check box for a category to ensure that all policies in that category are applied to the entireinstance.
When the Mandate Database Subscriptions check box is not selected for a policy category, the policycategory must be applied individually to each relevant portion of the server.
To open the Manage PolicyCategories dialog box, you should perform the following steps:
1. Open SQL Server Management Studio, and expand the Management node in the Object Explorer.
2. Right-click the Policy Management node, and click the Manage Categories option.

You should not clear the Mandate Database Subscriptions check box for the CorpPolicies category in the Manage Policy Categories dialog box. When you clear the Mandate Database Subscriptions check box for acategory, the policy category must be applied individually to each relevant portion of the server, which is not whatwas required in this scenario. To manually apply a policy, you can configure the policy with the On demandevaluation mode.

You should not ensure that the Server restriction option for all policies in the CorpPolicies category is set to None . The Server restriction option is used to limit a policy to a subset of the target types. This option isavailable on the General page of the Create New Policy dialog box or the Open Policy dialog box if you open anexisting policy. The Server restriction option cannot be used to apply the policy to the entire instance of the SQLServer 2008.

You should not ensure that the instance of the SQL Server 2008 is specified in the Against targets field for allpolicies in the CorpPolicies category because it is not possible to specify an instance of SQL Server 2008 in the Against targets field. The Against targets field is available on the General page of the Create New Policy dialog box or the Open Policy dialog box if you open an existing policy, and it is used to specify entities of theserver, such as databases.

Objective:
Maintaining SQL Server Instances

Sub-Objective:
Implement the declarative management framework (DMF).

References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > FeatureReference > SQL Server Management Studio F1 Help > Object Explorer F1 Help > Management Node (ObjectExplorer) > Policy Management Node (Object Explorer) > Manage Policy Categories Dialog Box MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Administering Servers by Using Policy-Based Management