Five Undocumented SQL Server 2014 Job Stored Procedures
SQL Server 2014 supports the following useful undocumented SQL Server job stored procedures:
- sp_MSget_jobstate
- sp_delete_all_msx_jobs
- sp_get_job_alerts
- sp_multi_server_job_summary
- sp_help_operator_jobs
One of these stored procedures exist in the master database (sp_MSget_jobstate), other
exist in the msdb database.
sp_MSget_jobstate
The sp_MSget_jobstate stored procedure returns the job state for the specified job.
Syntax
sp_MSget_jobstate [ @job_id = ] ‘job_id’
Arguments
[ @job_id = ] ‘job_id’
Is the job identifier. job_id is UNIQUEIDENTIFIER, with no default.
Return Code Values
0 (success) or 1 (failure).
Result Sets
| Column name | Type | Description |
| job_state | int | The state of the checked job. |
Remarks
You must start SQL Server agent to execute this stored procedure. This stored procedure exists
in the master database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Examples
This is the example to return the job state for the Test job:
USE msdb
DECLARE @job_id uniqueidentifier
SELECT @job_id = job_id from sysjobs where name = ‘Test’
EXEC master..sp_MSget_jobstate @job_id
sp_delete_all_msx_jobs
The sp_delete_all_msx_jobs SQL Server Agent stored procedure is used to delete all master
server (MSX) jobs.
Syntax
sp_delete_all_msx_jobs [ @msx_server = ] ‘msx_server’,
[ @jobs_deleted = ] ‘jobs_deleted’
Arguments
[ @msx_server = ] ‘msx_server’
Is the name of the master server. msx_server is sysname, with no default.
[ @jobs_deleted = ] ‘jobs_deleted’
This is the output parameter. After successful executing the sp_delete_all_msx_jobs stored
procedure it contains the number of the deleted jobs. jobs_deleted is INT, with default NULL.
Return Code Values
None.
Result Sets
None.
Remarks
After successful executing the sp_delete_all_msx_jobs stored procedure the @jobs_deleted
parameter contains the number of the deleted jobs. This stored procedure exists in the
msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Examples
This is the example to delete all msx jobs from the TestSrv server:
USE msdb
EXEC sp_delete_all_msx_jobs @msx_server = ‘TestSrv’
sp_get_job_alerts
The sp_get_job_alerts stored procedure returns the alert identifier, alert name, alert state
(enable or disable) and alert type (SQL Server event alert, WMI event alert or performance
condition alert) for the specified job.
Syntax
sp_get_job_alerts [ @job_id = ] ‘job_id’,
[ @job_name = ] ‘job_name’
Arguments
[ @job_id = ] ‘job_id’
Is the job identifier. job_id is UNIQUEIDENTIFIER, with default NULL.
You must supply either @job_id or @job_name to identify the job.
[ @job_name = ] ‘job_name’
The name of the job. job_name is sysname, with default NULL.
You must supply either @job_id or @job_name to identify the job.
Return Code Values
0 (success) or 1 (failure).
Result Sets
| Column name | Type | Description |
| id | int | The alert identifier. |
| name | sysname | The alert name. |
| enabled | tinyint | The status of the alert (0 – disabled, 1 – enabled) |
| type | int | The alert type (1 – SQL Server event alert, 2 – performance condition alert, 3 – WMI event alert) |
Remarks
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Examples
This is the example to return the alerts information for the Test job:
USE msdb
EXEC sp_get_job_alerts @job_name = ‘Test’
sp_multi_server_job_summary
The sp_multi_server_job_summary SQL Server Agent stored procedure returns the job’s state
(enable or disable), category name, the number of target servers, the number of pending
download instructions, the number of download errors and the number of execution failures
for the specified multi-server job.
Syntax
sp_multi_server_job_summary [ @job_id = ] ‘job_id’,
[ @job_name = ] ‘job_name’
Arguments
[ @job_id = ] ‘job_id’
Is the job identifier. job_id is UNIQUEIDENTIFIER, with default NULL.
You must supply either @job_id or @job_name to identify the job.
[ @job_name = ] ‘job_name’
The name of the job. job_name is sysname, with default NULL.
You must supply either @job_id or @job_name to identify the job.
Return Code Values
0 (success) or 1 (failure).
Result Sets
| Column name | Type | Description |
| job_id | int | The job identifier. |
| job_name | tinyint | The job name. |
| enabled | tinyint | The status of the job (0 – disabled, 1 – enabled). |
| category_name | sysname | The category name. |
| pending_download_instructions | int | The number of the pending download instructions |
| download_errors | int | The number of the download errors. |
| execution_failures | int | The number of the execution failures. |
Remarks
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Examples
This is the example to return the information for the Test multi-server job:
USE msdb
EXEC sp_multi_server_job_summary @job_name = ‘Test’
sp_help_operator_jobs
The sp_help_operator_jobs SQL Server Agent stored procedure returns the jobs the specified
operator is responsible for.
Syntax
sp_help_operator_jobs [ @operator_name = ] ‘operator_name’
Arguments
[ @operator_name = ] ‘operator_name’
Is the operator name. operator_name is sysname, with default NULL.
Return Code Values
0 (success) or 1 (failure).
Result Sets
| Column name | Type | Description |
| job_id | int | The job identifier. |
| job_name | sysname | The job name. |
| notify_level_email | int | Indicates whether operator receive notification through e-mail |
| notify_level_netsend | int | Indicates whether operator receive notification through net send |
| notify_level_page | int | Indicates whether operator receive notification through pager |
Remarks
This stored procedure exists in the msdb database.
Permissions
By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.
Examples
This is the example to return the jobs the Alex operator is responsible for:
USE msdb
EXEC sp_help_operator_jobs @operator_name = ‘Alex’