Sql-server – Find Job Calling Another Job

jobssql serversql-server-2008

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'

Best Answer

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.

Related Question