You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company.
You create a maintenance job that runs two statements against the CK_Sales database and schedule the job to run every evening at 7:30 P.M. The following morning you confirm that the job ran on schedule. A month later you discover that the job has failed and no longer runs on schedule. You need to determine the cause of the problem.
What should you do?
A.
Manually run the job and use the SQL Server Profiler to monitor the execution of the job. Save the results to a trace file.
B.
Run the sp_trace_setstatus stored procedure.
C.
Search the Application log in Event Viewer for SQL-related error messages.
D.
Run the sp_help_jobhistory stored procedure.
Explanation:
The sp_help_jobhistory stored procedure returns information about the cause of a job failure.
Incorrect Answers:
A: SQL Server Profiler does not return information about the cause of a job failure.
B: The sp_trace_setstatus stored procedure is used to start, stop or close a trace. It does not return information about the cause of a job failure.
C: You can use the Application log to determine the cause of a job failure as the Application log does not record such events.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: sp_help_jobhistory Microsoft SQL Server 2005 Books Online (2006), Index: sp_trace_setstatus Microsoft SQL Server 2005 Books Online (2006), Index: error messages [SQL Server]