Skip to content

Latest commit

 

History

History
114 lines (81 loc) · 3.95 KB

sp-help-jobserver-transact-sql.md

File metadata and controls

114 lines (81 loc) · 3.95 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sp_help_jobserver (Transact-SQL)
sp_help_jobserver returns information about the server for a given job.
markingmyname
maghan
randolphwest
05/14/2024
sql
system-objects
reference
sp_help_jobserver
sp_help_jobserver_TSQL
sp_help_jobserver
TSQL
>=sql-server-2016 || >=sql-server-linux-2017

sp_help_jobserver (Transact-SQL)

[!INCLUDE SQL Server]

Returns information about the server for a given job.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_help_jobserver
    [ [ @job_id = ] 'job_id' ]
    [ , [ @job_name = ] N'job_name' ]
    [ , [ @show_last_run_details = ] show_last_run_details ]
[ ; ]

Arguments

[ @job_id = ] 'job_id'

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

Either @job_id or @job_name must be specified, but both can't be specified.

[ @job_name = ] N'job_name'

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

Either @job_id or @job_name must be specified, but both can't be specified.

[ @show_last_run_details = ] show_last_run_details

Whether the last-run execution information is part of the result set. @show_last_run_details is tinyint, with a default of 0.

  • 0 doesn't include last-run information.
  • 1 includes last-run information.

Return code values

0 (success) or 1 (failure).

Result set

Column name Data type Description
server_id int Identification number of the target server.
server_name nvarchar(30) Computer name of the target server.
enlist_date datetime Date the target server enlisted into the master server.
last_poll_date datetime Date 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 extra columns.

Column name Data type Description
last_run_date int Date the job last started execution on this target server.
last_run_time int Time the job last started execution on this server.
last_run_duration int Duration of the job the last time it ran on this target server (in seconds).
last_outcome_message nvarchar(1024) Describes the last outcome of the job.
last_run_outcome int Outcome of the job the last time it ran on this server:

0 = Failed
1 = Succeeded
3 = Canceled
5 = Unknown

Permissions

[!INCLUDE msdb-execute-permissions]

Other users must be granted one of the following [!INCLUDE ssNoVersion] 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.

Examples

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

USE msdb;
GO

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

Related content