Find Job Calling Another Job

Question

We have a job that is not scheduled to run but was kicked off by what we suspect was another job. There is no job history but it does appear in the error logs. How can we find out if this is being called by another job? I know that we could use sp_start_job to immediately call a job within a job step but, if that is the case, how can we find it?

And, why would there not be any history for the job that ran?

Error log entry:

2017-05-01 10:01:50.40 spid6s         SPID: 131 ECID: 0 Statement Type: ALTER INDEX Line #: 1
2017-05-01 10:01:50.40 spid6s         Input Buf: Language Event: EXECUTE [msdb]. dbo.IndexOptimize @Databases = 'Database1,Database2'

Answer 1

How can we find out if this is being called by another job?

Search all your job steps for the string with job name that ran by other job (at least that is what you suspect). This query is from this website.

USE [msdb]
GO
SELECT  j.job_id,
    s.srvname,
    j.name,
    js.step_id,
    js.command,
    j.enabled 
FROM    dbo.sysjobs j
JOIN    dbo.sysjobsteps js
    ON  js.job_id = j.job_id 
JOIN    master.dbo.sysservers s
    ON  s.srvid = j.originating_server_id
WHERE   js.command LIKE N'%KEYWORD_SEARCH%'
GO

why would there not be any history for the job that ran?

Depending of how your job history is set up. See my blog post. Another good reference here.

Answer 2

Just to answer your basic question, if you have a job running sp_start_job you can find it by looking in the msdb.dbo.sysjobsteps system table. Something like this will give you a list of all jobs that run the sp_start_job SP and the command that contains it.

SELECT sysjobs.name, sysjobsteps.* 
FROM msdb.dbo.sysjobsteps
JOIN msdb.dbo.sysjobs
    ON sysjobsteps.job_id = sysjobs.job_id
WHERE sysjobsteps.command like '%sp_start_job%'

Unfortunately I don't know of any history that will tell you where a job is actually being called from.

Answer 3

You saw the error in the SQL Server error logs, not a special log tied to jobs, correct? Then, this stored procedure (which you probably do use in a job) may have been run manually (for example, from SSMS). In that, case, there would be no job history involved (because no job was run).

If IndexOptimize was run manually instead of through a job, your only chances at any sort of information lie in metadata (for example, you might be able to find a plan in the plan cache involving that stored proc; that might tell you something), or in the logging options for IndexOptimize itself. This Ola Hallengren index maintenance proc includes an option to log commands to a table (CommandLog). You can check that table and see if any commands were logged for today.

Answer 4

you could use the script provided here by Michael Goldshteyn with additional data cleansing. also the filter provided by Kenneth Fisher to use "sp_start_job" is used in the following script.

SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sJSTP].[step_uid] AS [StepID]
, [sJSTP].[step_id] AS [StepNo]
, [sJSTP].[step_name] AS [StepName]
, z.Call_To_Job
, CASE [sJSTP].[subsystem]
    WHEN 'ActiveScripting' THEN 'ActiveX Script'
    WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
    WHEN 'PowerShell' THEN 'PowerShell'
    WHEN 'Distribution' THEN 'Replication Distributor'
    WHEN 'Merge' THEN 'Replication Merge'
    WHEN 'QueueReader' THEN 'Replication Queue Reader'
    WHEN 'Snapshot' THEN 'Replication Snapshot'
    WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
    WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
    WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
    WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
    WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
    ELSE sJSTP.subsystem
  END AS [StepType]
, [sPROX].[name] AS [RunAs]
, [sJSTP].[database_name] AS [Database]
, [sJSTP].[command] AS [ExecutableCommand]
, CASE [sJSTP].[on_success_action]
    WHEN 1 THEN 'Quit the job reporting success'
    WHEN 2 THEN 'Quit the job reporting failure'
    WHEN 3 THEN 'Go to the next step'
    WHEN 4 THEN 'Go to Step: ' 
                + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) 
                + ' ' 
                + [sOSSTP].[step_name]
  END AS [OnSuccessAction]
, [sJSTP].[retry_attempts] AS [RetryAttempts]
, [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
, CASE [sJSTP].[on_fail_action]
    WHEN 1 THEN 'Quit the job reporting success'
    WHEN 2 THEN 'Quit the job reporting failure'
    WHEN 3 THEN 'Go to the next step'
    WHEN 4 THEN 'Go to Step: ' 
                + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) 
                + ' ' 
                + [sOFSTP].[step_name]
  END AS [OnFailureAction]
FROM [msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
    ON [sJSTP].[job_id] = [sJOB].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
    ON [sJSTP].[job_id] = [sOSSTP].[job_id]
    AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
    ON [sJSTP].[job_id] = [sOFSTP].[job_id]
    AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
    ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
 CROSS APPLY 
    (
    VALUES (
        SUBSTRING([sJSTP].[command] , PATINDEX ('%sp_start_job%', [sJSTP].[command]), LEN([sJSTP].[command])) /*Strip the sp_start_job phrase*/
        )
)X (Job_Search)
CROSS APPLY 
    (
    VALUES (
        SUBSTRING(X.Job_Search,PATINDEX('%''%',X.Job_Search) + 1 ,LEN(X.Job_Search)) /*Find the first quote that identifies the job name that is being executed executed*/
        )
) Y(ModifiedCommand)
CROSS APPLY 
    (
    VALUES (
        SUBSTRING(ModifiedCommand , 1 , PATINDEX('%''%',ModifiedCommand )-1) /*Find the closing quote that identifies the job name that is being executed*/
        )
) Z(Call_To_Job)
WHERE 1=1
AND [sJSTP].[command] LIKE '%sp_start_job%' 
--AND Call_To_Job like '%Job name%' /*Uncomment to specify the job being called*/
ORDER BY [JobName], [StepNo]