Provides information about the jobs for servers in the multiserver administration domain.
sp_help_jobhistory [ [ @job_id= ] job_id ] [ , [ @job_name= ] 'job_name' ] [ , [ @step_id= ] step_id ] [ , [ @sql_message_id= ] sql_message_id ] [ , [ @sql_severity= ] sql_severity ] [ , [ @start_run_date= ] start_run_date ] [ , [ @end_run_date= ] end_run_date ] [ , [ @start_run_time= ] start_run_time ] [ , [ @end_run_time= ] end_run_time ] [ , [ @minimum_run_duration= ] minimum_run_duration ] [ , [ @run_status= ] run_status ] [ , [ @minimum_retries= ] minimum_retries ] [ , [ @oldest_first= ] oldest_first ] [ , [ @server= ] 'server' ] [ , [ @mode= ] 'mode' ]
The actual column list depends on the value of mode. The most comprehensive set of columns is shown below and is returned when mode is FULL.
History entry identification number.
Job identification number.
Step identification number (will be 0 for a job history).
Step name (will be NULL for a job history).
For a Transact-SQL step, the most recent Transact-SQL error number encountered while running the command.
For a Transact-SQL step, the highest Transact-SQL error severity encountered while running the command.
Job or step history message.
Outcome of the job or step.
Date the job or step began executing.
Time the job or step began executing.
Elapsed time in the execution of the job or step in HHMMSS format.
Operator who was e-mailed regarding this job (is NULL for step history).
Operator who was sent a network message regarding this job (is NULL for step history).
Operator who was paged regarding this job (is NULL for step history).
Number of times the step was retried (always 0 for a job history).
Server the step or job executes on. Is always (local).
By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
Members of the SQLAgentUserRole database role can only view the history for jobs that they own.
A. Listing all job information for a job
The following example lists all job information for the NightlyBackups job.
USE msdb ; GO EXEC dbo.sp_help_jobhistory @job_name = N'NightlyBackups' ; GO
B. Listing information for jobs that match certain conditions
The following example prints all columns and all job information for any failed jobs and failed job steps with an error message of 50100 (a user-defined error message) and a severity of 20.
USE msdb GO EXEC dbo.sp_help_jobhistory @sql_message_id = 50100, @sql_severity = 20, @run_status = 0, @mode = N'FULL' ; GO