Which query will you use to achieve the objective?

You are the database administrator for a major shipping company. You manage all the SQL Server 2005 instances of the company. For one of your instances, you have created jobs to perform regular administrative activities. Some of these jobs in the database fail because the server went down due to a power failure. You want to analyze 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 table resides in the msdb database and contains columns, such as job_id, step_id, and step_name, which identify jobs and the steps involved in the jobs. To retrieve the details of tasks being performed by the failed jobs, you should 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 column indicates 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 contains information, such as the time the last step in the job executed and the time at which the job is scheduled to run next.

Objective: Maintaining SQL Server Instances Sub-Objective: Manage SQL Server Agent jobs.

References: MSDN > MSDN Library> Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > System Tables (Transact-SQL) > SQL Server Agent Tables (Transact-SQL) > sysjobhistory (Transact-SQL)