sp_help_jobserver (Transact-SQL)


Returns information about the server for a given job.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

     { [ @job_id = ] job_id   
     | [ @job_name = ] 'job_name' }  
     [ , [ @show_last_run_details = ] show_last_run_details ]  

[ @job_id= ] job_id
The job identification number for which to return information. job_id is uniqueidentifier, with a default of NULL.

[ @job_name= ] 'job_name'
The job name for which to return information. job_name is sysname, with a default of NULL.

System_CAPS_ICON_note.jpg Note

Either job_id or job_name must be specified, but both cannot be specified.

[ @show_last_run_details= ] show_last_run_details
Is whether the last-run execution information is part of the result set. show_last_run_details is tinyint, with a default of 0. 0 does not include last-run information, and 1 does.

0 (success) or 1 (failure)

Column nameData typeDescription
server_idintIdentification number of the target server.
server_namenvarchar(30)Computer name of the target server.
enlist_datedatetimeDate the target server enlisted into the master server.
last_poll_datedatetimeDate the target server last polled the master server.

If sp_help_jobserver is executed with show_last_run_details set to 1, the result set has these additional columns.

Column nameData typeDescription
last_run_dateintDate the job last started execution on this target server.
last_run_timeintTime the job last started execution on this server.
last_run_durationintDuration of the job the last time it ran on this target server (in seconds).
last_outcome_messagenvarchar(1024)Describes the last outcome of the job.
last_run_outcomeintOutcome of the job the last time it ran on this server:

 0 = Failed

 1 = Succeeded

 3 = Canceled

 5 = Unknown

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:

  • SQLAgentUserRole

  • SQLAgentReaderRole

  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Members of SQLAgentUserRole can only view information for jobs that they own.

The following example returns information, including last-run information, about the NightlyBackups job.

USE msdb ;  
EXEC dbo.sp_help_jobserver  
    @job_name = N'NightlyBackups',  
    @show_last_run_details = 1 ;  

sp_add_jobserver (Transact-SQL)
sp_delete_jobserver (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions