sp_sjobhistory

Description

Lists or deletes job history and job output logs. You can use this procedure to look at the results of scheduled jobs that have completed. You can also use it during administration of the job history and job output logs.

Syntax

sp_sjobhistory @name=’...’, @option=’...’

Parameters

name: The name or ID of the scheduled job, a job, or a runid.

The @name argument can be used to restrict the scope to the history of a single scheduled job, all scheduled jobs using a particular job, or a single run of a scheduled job. By default, @name is the name or ID or a scheduled job. To specify the name or ID of a job or as a runid, the @name argument is prefixed with jname or runid.

option: Specifies a comma-separated list of options, and option_name=option_value pairs that define the action to perform on a filtered set of the job history and job output.

list – lists the job history fields for the jobs matching the filter conditions.

list_short – lists a subset of the job history fields for the jobs matching the filter conditions.

drop – deletes the job history entries and job output entries for the jobs matching the filter conditions.

list_output – lists the job output for the jobs matching the filter conditions.

drop_output – deletes the job output entries for the jobs matching the filter conditions. The corresponding job history entries are not deleted.

all_users – includes all user jobs in the scope. The caller must have the js_admin_role.

user – restricts the scope to jobs run by or on behalf of this user. If the user specified is not the caller’s server user name, the caller must have the js_admin_role.

owner – restricts the scope to jobs run using this server user name. If the owner specified is not the caller’s server user name, the caller must have the js_admin_role.

age – restricts the scope to jobs recorded earlier than this number of days ago.

minsize – restricts the scope to jobs with more than minsize bytes of output.

force – allows the history and output to be deleted for running jobs.

Returns

Returns 0 on success or an error code.

Examples

Example 1

The following example lists the concise history of the job called “orders_processed_report” that was run for the caller:

sp_sjobhistory @name=’jname=orders_processed_report’, @option=’list_short’

Example 2

The following example deletes the history and job output for the scheduled job that ran with runid 12389:

sp_sjobhistory @name=’runid=12389’, @option=’drop’

Example 3

The following example lists all the caller’s job history that has job output greater than 10,000 bytes:

sp_sjobhistory @option=’list,minsize=10000’

Example 4

The following example drops all the caller job history and job output that has job output greater than 20,000 bytes:

sp_sjobhistory @option=’drop,minsize=20000’

Example 5

The following example lists the history of the job called “load_sales_data” that was run by any user:

sp_sjobhistory @name=’jname=load_sales_data’, @option=’list,all_users’

Example 6

The following example lists the concise history of all the jobs run by the user called “mary”:

sp_sjobhistory @option=’list_short,user=mary’

Usage

To protect against accidental removal of all the job history or output logs, an error occurs when you call sp_sjobhistory with the drop option and no filter arguments; you must supply at least one filter argument.
sp_sjobhistory does not delete job output log entries for jobs that are currently running. These entries are silently ignored during the deletion process, unless the runid argument specifies an entry for a running job. In this case, sp_sjobhistory returns an error.

The following table shows the results from the js_history table when you use the list argument:

**Table 5-2: Output returned from the list argument**
Column	Type
job_runid	int
job_name	JS_DESC
job_state	char(2)
job_end	datetime
job_user_code	int
job_os_code	int
job_user_req	SUSER_NAME
job_long_message	JS_LMSG
sjob_id	JS_NAME_ID
sched_name	JS_NAME_ID
job_start	datetime
job_exit_code	int
job_atat_error	int
job_user_run	SUSER_NAME
job_short_message	JS_SMSG
job_size	int

The following table shows the results from the js_history table when you use the list_short argument:

**Table 5-3: Output returned from the list_short argument**
Column	Type
job_runid	int
job_name	JS_DESC
job_state	char(2)
job_end	datetime
job_user_code	int
job_size	int
sjob_id	JS_NAME_ID
sched_name	JS_NAME_ID
job_start	datetime
job_exit_code	int
job_atat_error	int

The following table shows the results from the js_history table when you use the list_output argument:

**Table 5-4: Output returned from the list_output argument**
Column	Type
job_runid	int
job_size	int
job_name	JS_DESC
job_output	JS_OUTPUT