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.

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

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