Which Transact-SQL should you use?

You are the database administrator for a major shipping company. You manage all the SQL Server 2008 databases of the company.
You have created different jobs in the database to perform different administrativefunctions.
Due to recent scheduling changes, you must reschedule the jobs to be executed at a different time.
You want toidentify details of the last job that was successfully executed on the server.
Which Transact-SQL should you use?

A.
DECLARE @num AS int;
SET @num = ‘1’;
SELECT TOP(@num) job_id, run_status, run_date, run_time
FROM master.dbo.sysjobhistory
WHERE run_status = 2
ORDER BY run_date, run_time;

B.
DECLARE @num AS int;
SET @num = ‘1’;
SELECT TOP(@num) job_id, run_status, run_date, run_time
FROM master.dbo.sysjobhistory
WHERE run_status = 1
ORDER BY run_date DESC, run_time DESC;

C.
DECLARE @num AS int;
SET @num = ‘1’;
SELECT TOP(@num) job_id, run_status, run_date, run_time
FROM msdb.dbo.sysjobhistory
WHERE run_status = 1
ORDER BY run_date, run_time;

D.
DECLARE @num AS int;
SET @num = ‘1’;
SELECT TOP(@num) job_id, run_status, run_date, run_time
FROM msdb.dbo.sysjobhistory
WHERE run_status = 1
ORDER BY run_date DESC, run_time DESC;

Explanation:

You should use the following Transact-SQL: DECLARE @num AS int; SET @num = ‘1’; SELECT TOP(@num) job_id, run_status, run_date, run_time FROM msdb.dbo.sysjobhistory
WHERE run_status = 1 ORDER BY run_date DESC, run_time DESC; The dbo.sysjobhistory system table provides the history of previously executed jobs. The table is in the msdb database; therefore, you should only query the msdb database. This is accomplished in the query by prependingthe database name to the table name. The dbo.sysjobhistory table contains columns, such as run_date and run_time , which indicate the date and time at which the jobs were executed, respectively. To retrieve the detailsof the jobs executed last, you should sort the result set of the query in descending order on the run_date and run_time columns. To retrieve the jobs that executed successfully, you should use the search for a value of 1 for run_status column in the WHERE clause condition. A value of 1 in the run_status column indicates jobs thathave been completed successfully. The TOP expression used in the query uses a value of 1. This indicates thatonly the first row should be retrieved from the result set. You should not use the following Transact-SQL: DECLARE @num AS int;
SET @num = ‘1’; SELECT TOP(@num) job_id, run_status, run_date, run_time FROM master.dbo.sysjobhistory WHERE run_status = 2 ORDER BY run_date, run_time; This query will return an error because the dbo.sysjobhistory table is located in the msdb database, not in the master database. In addition, specifying run_status = 2 in the WHERE clause condition will retrieve jobs with aretry status. In this scenario, you must retrieve jobs that completed successfully. Therefore, you should use the run_status = 1 in the WHERE clause condition. You should not use the following Transact-SQL: DECLARE @num AS int; SET @num = ‘1’; SELECT TOP(@num) job_id, run_status, run_date, run_time FROM master.dbo.ysjobhistory
WHERE run_status = 1 ORDER BY run_date DESC, run_time DESC; This query will return an error because the dbo.sysjobhistory table is located in the msdb database, not in the
master database. You should not use the following Transact-SQL: DECLARE @num AS int; SET @num = ‘1’ SELECT TOP(@num) job_id, run_status, run_date, run_time FROM msdb.dbo.sysjobhistory WHERE run_status = 1 ORDER BY run_date, run_time; Because you omitted the DESC keyword in the ORDER BY clause of the query, the rows returned will be sortedin ascending order by default. In this scenario, the rows returned by the query should be sorted in descendingorder by using the DESC keyword in the ORDER BY
clause. Sorting the rows in a descending order will retrievethe job that was the last to be executed successfully. If you sort the rows in the ascending order, the first job thatwas executed successfully will be returned.

Objective:
Monitoring and Troubleshooting SQL Server

Sub-Objective:
Identify SQL Agent job execution problems.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Tables (Transact-SQL) > SQL Server Agent Tables (Transact-SQL) > sysjobhistory(Transact-SQL)