You manage an instance of SQL Server 2008 named SQL1 . SQL1 contains a database named Products thatstores product data for the company.
You create a maintenance job for the Products database.
The job runs three Transact-SQL statements to perform maintenance tasks on the Products database.
You configure the jobto run every evening at 8:00 P.M. You verify the next day that the job executed as scheduled.
After three weeks, you discover that the job has failed and is no longer running as scheduled.
You want todetermine the cause of the failure. Which system stored procedure should you use?

A.
sp_help_jobactivity

B.
sp_help_jobhistory

C.
sp_help_jobsteplog

D.
sp_help_notification

Explanation:

You should use the sp_help_jobhistory system stored procedure. The sp_help_jobhistory system storedprocedure provides information about the jobs configured on SQL servers in a multiserver administration domain.You can call this stored procedure to obtain information about the cause of a job failure.
You should not use the sp_help_jobactivity system stored procedure because this stored procedure does notprovide information about the cause of a SQL Server Agent job failure. The sp_help_jobactivity system storedprocedure provides information about the runtime state of SQL Server Agent jobs. You should not use the sp_help_jobsteplog system stored procedure because this stored procedure does notprovide information about the cause of a SQL Server Agent job failure. The sp_help_jobsteplog system storedprocedure provides metadata for a particular SQL Server Agent job step. You should not use the sp_help_notification system stored procedure because this stored procedure does notprovide information about the cause of a SQL Server Agent job failure. The sp_help_notification system storedprocedure returns a list of alerts for a given operator or a list of operators for a given alert.

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 Stored Procedures (Transact-SQL) > SQL Server Agent Stored Procedures (Transact-SQL)> sp_help_jobhistory (Transact-SQL)