Eight ways to run a SQL Server Agent job, and when each one is the right choice

#1. SSMS

Right-click the job and choose Start Job at Step.... For a multi-step job you pick the step, which is handy for resuming after a failure. Job Activity Monitor shows the result.

#2. T-SQL: sp_start_job

EXEC msdb.dbo.sp_start_job @job_name = N'Finance - ETL - Load GL';
-- start at a specific step (resume after a failure)
EXEC msdb.dbo.sp_start_job @job_name = N'Finance - ETL - Load GL', @step_name = N'Merge into dw';

sp_start_job is asynchronous. It returns as soon as Agent accepts the request ("Job started successfully"), not when the job finishes. It fails if the job is already running.

#Wait for completion

DECLARE @job sysname = N'Finance - ETL - Load GL', @start datetime = GETDATE();
EXEC msdb.dbo.sp_start_job @job_name = @job;
WAITFOR DELAY '00:00:05';
WHILE EXISTS (SELECT 1 FROM msdb.dbo.sysjobactivity a JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
              WHERE j.name = @job AND a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
                AND a.start_execution_date >= DATEADD(second, -5, @start) AND a.stop_execution_date IS NULL)
    WAITFOR DELAY '00:00:10';

-- outcome of the run we started
SELECT TOP (1) run_status, message FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE j.name = @job AND h.step_id = 0 ORDER BY h.instance_id DESC;

#3. From another job (job chaining)

Add a final T-SQL step that calls sp_start_job for the next job. Because the call doesn't wait, the first job finishes immediately. That's fine for "kick off the next stage", but use the wait loop above if the parent must know the result. For complex dependencies, an external scheduler or orchestrator is often cleaner than long chains.

#4. PowerShell

# dbatools: start and wait
Start-DbaAgentJob -SqlInstance sql01 -Job 'Finance - ETL - Load GL' -Wait

# SqlServer module (SMO)
Import-Module SqlServer
$job = Get-SqlAgentJob -ServerInstance sql01 -Name 'Finance - ETL - Load GL'
$job.Start()

# plain T-SQL over Invoke-Sqlcmd
Invoke-Sqlcmd -ServerInstance sql01 -Query "EXEC msdb.dbo.sp_start_job @job_name = N'Finance - ETL - Load GL';" -TrustServerCertificate

Use this from deployment pipelines, runbooks (Azure Automation, Ansible) or enterprise schedulers.

#5. sqlcmd from a batch file or another scheduler

sqlcmd -S sql01 -E -b -Q "EXEC msdb.dbo.sp_start_job @job_name = N'Finance - ETL - Load GL';"

-b returns a non-zero exit code if the call fails (for example, job not found or already running), so the calling scheduler sees the error.

#6. Schedules

-- one-off run tonight at 22:30
EXEC msdb.dbo.sp_add_jobschedule @job_name = N'Finance - ETL - Load GL', @name = N'One-off 2026-10-23 22:30',
     @freq_type = 1, @active_start_date = 20261023, @active_start_time = 223000;

#7. In response to an Agent alert

An alert can execute a job as its response. For example, when the log is nearly full, run a log backup job:

EXEC msdb.dbo.sp_add_alert @name = N'Sales log 85% full',
     @performance_condition = N'SQLServer:Databases|Percent Log Used|Sales|>|85',
     @job_name = N'DBA - Backup - Sales LOG', @delay_between_responses = 600;

On a named instance, the counter object is MSSQL$INSTANCENAME:Databases.

#8. From an application, with least privilege

Don't make the application login sysadmin. Options:

• Add the login to SQLAgentOperatorRole in msdb. It can then start any local job, which may be too broad.
• Better: a wrapper procedure signed with a certificate, which can start only an allow-listed job:

USE msdb;
CREATE CERTIFICATE JobStarterCert ENCRYPTION BY PASSWORD = N'Use-a-real-strong-password!' WITH SUBJECT = N'Start allow-listed jobs';
CREATE USER JobStarterCertUser FROM CERTIFICATE JobStarterCert;
ALTER ROLE SQLAgentOperatorRole ADD MEMBER JobStarterCertUser;
GO
CREATE OR ALTER PROCEDURE dbo.usp_start_allowed_job @job_name sysname
AS
BEGIN
    IF @job_name NOT IN (N'Finance - ETL - Load GL', N'Finance - Report - Refresh')
        THROW 50010, 'Job not allowed.', 1;
    EXEC msdb.dbo.sp_start_job @job_name = @job_name;
END;
GO
ADD SIGNATURE TO dbo.usp_start_allowed_job BY CERTIFICATE JobStarterCert WITH PASSWORD = N'Use-a-real-strong-password!';
GRANT EXECUTE ON dbo.usp_start_allowed_job TO [CORP\svc-finance-app];   -- user must exist in msdb

Re-sign the procedure every time you alter it.

#Which one to use