When you first create a SQL Server Agent job, you might want to do a quick test to see if it’s going to actually run without any issues.

This is quite straightforward when using the SSMS GUI, but how do you do it in T-SQL?

Answer: The sp_start_job stored procedure.

Example

Here’s an example to demonstrate:

USE msdb;  
GO  
  
EXEC sp_start_job N'BackupKrankyKranesDB';
GO

In this case I ran a job called BackupKrankyKranesDB.

The procedure does not return any result sets. However, if you run it in SSMS, you might see a message like this:

Job 'BackupKrankyKranesDB' started successfully.
 Completion time: 2020-12-13T19:54:18.3503521-08:00

Parameters

You can also provide the parameter names. In that case, I could have done this:

USE msdb;  
GO  
  
EXEC sp_start_job @job_name = N'BackupKrankyKranesDB';
GO

You can provide either the job name or the job ID (but not both). The job ID is a uniqueidentifier with a default of NULL.

Therefore, we could have alternatively done this:

USE msdb;  
GO  
  
EXEC sp_start_job @job_id = '4FAE7031-36E8-4934-81E6-0D561F375627';
GO

That is the job ID of the above job. Obviously you’d need to use the ID of the job you need to run. You can use the sp_help_job stored procedure to get the job ID.

The Syntax

The actual syntax goes like this:

sp_start_job   
     {   [@job_name =] 'job_name'  
       | [@job_id =] job_id }  
     [ , [@error_flag =] error_flag]  
     [ , [@server_name =] 'server_name']  
     [ , [@step_name =] 'step_name']  
     [ , [@output_flag =] output_flag]

See sp_start_job on the Microsoft website for an explanation of each argument.

View Job History

Now that we’ve run the job, we can now use sp_help_jobhistory to check that it was added to the job history:

EXEC sp_help_jobhistory 
	@job_name = 'BackupKrankyKranesDB',
	@mode = 'FULL';

Using @mode = 'FULL' ensures that we get more detailed information.

Here’s what I got after running the job:

See sp_help_jobhistory on the Microsoft website for more information on this stored procedure.

Also see 4 Ways to Get SQL Server Job History for more options for returning job history.