Hidden job chains: finding SQL Agent jobs that start other jobs

#Why it matters

You disable "ETL - Load Staging" for maintenance, and three downstream jobs silently stop too, because they were started from its last step. Or you migrate a server and forget a job that existed only to be started by another job.

#1. Steps that call sp_start_job directly

SELECT parent.name  AS calling_job,
       s.step_id, s.step_name,
       s.command
FROM msdb.dbo.sysjobsteps s
JOIN msdb.dbo.sysjobs parent ON parent.job_id = s.job_id
WHERE s.command LIKE '%sp[_]start[_]job%'
ORDER BY parent.name, s.step_id;

Extract the target job name from the command:

SELECT parent.name AS calling_job, child.name AS started_job, s.step_id
FROM msdb.dbo.sysjobsteps s
JOIN msdb.dbo.sysjobs parent ON parent.job_id = s.job_id
JOIN msdb.dbo.sysjobs child  ON s.command LIKE '%' + child.name + '%'
WHERE s.command LIKE '%sp[_]start[_]job%'
  AND child.job_id <> parent.job_id;

This catches calls by name. Calls by @job_id need the GUID: search for CONVERT(varchar(36), child.job_id) the same way.

#2. Steps that call other tools to start jobs

PowerShell or CmdExec steps can start jobs too:

SELECT j.name, s.step_id, s.subsystem, LEFT(s.command, 300) AS command_start
FROM msdb.dbo.sysjobsteps s JOIN msdb.dbo.sysjobs j ON j.job_id = s.job_id
WHERE s.command LIKE '%Start-DbaAgentJob%'
   OR s.command LIKE '%Start()%'            -- SMO Job.Start()
   OR s.command LIKE '%sqlcmd%sp_start_job%';

#3. Procedures that start jobs

The job step may call dbo.usp_RunNextStage, which then calls sp_start_job. Search module definitions in every database:

EXEC sp_MSforeachdb N'
USE [?];
SELECT DB_NAME() AS db, OBJECT_SCHEMA_NAME(object_id) + ''.'' + OBJECT_NAME(object_id) AS module
FROM sys.sql_modules WHERE definition LIKE ''%sp[_]start[_]job%'';';

Then search step commands for those procedure names.

#4. Alerts that run jobs

SELECT a.name AS alert_name, a.message_id, a.severity, j.name AS job_started
FROM msdb.dbo.sysalerts a
JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id;

#5. Linked servers and other instances

A step can start a job on another server: EXEC [SQL02].msdb.dbo.sp_start_job .... Search commands for msdb.dbo.sp_start_job preceded by a four-part name, and check the other server too.

#Draw the chain

Put the results in one table (caller → callee → how) and keep it with your documentation. A simple text diagram in your wiki works:

ETL - Extract  ──step 4──▶  ETL - Transform  ──step 2──▶  ETL - Publish
Alert: Sev 17  ──────────▶  DBA - Capture Diagnostics

Warning

sp_start_job is asynchronous. The caller doesn't wait for the child, so the parent job "succeeds" even if the child fails. Notifications must be on the child job too.

#Prevent it next time

• Prefer one job with several steps over chains of jobs where possible.
• If you need cross-job dependencies, use a table-driven approach with explicit status (see the job dependencies post).
• Mention callers in the child job's description.