5

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'
5
  • Looks like my question was truncated. I know we can use sp_start_job to immediately call a job within a job step but, if that is the case, how can we find it?
    – Marvel
    May 1 '17 at 16:17
  • & why would there not be any history for the job that ran?
    – Marvel
    May 1 '17 at 16:18
  • 1
    Why do you think this was kicked off by a job and not by a person? May 1 '17 at 18:20
  • That is what I am beginning to think, Aaron. Someone must have executed the Hallengren stored proc but why & why the 2 databases that are specifically excluded from the weekly job but included in the job that is not scheduled & has no history? I suspect I will never know the answer but I thank you all for your input.
    – Marvel
    May 1 '17 at 20:06
  • Marvel - Have a look at some of the answers on this SO post and simply search for a string in all stored procs accordingly to find whatever you need to find (e.g. other SP name, etc.) to help narrow down your search since it's indicated that potentially other procs are executing SQL Agent jobs: stackoverflow.com/questions/14704105/… . . . follow one of the answer to find which ones it could be potentially. May 2 '17 at 1:39
3

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.

2
  • "why would there not be any history for the job that ran?" Absolutely no clue. When I query for job history there are no results.
    – Marvel
    May 1 '17 at 17:54
  • Did you check how your job history retention is set up? May 1 '17 at 17:55
1

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.

0

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.

1
  • @RDFuzz Yes, it is the Hallegren script but the 2 jobs that reference it have no history associated with them for this morning. One is not scheduled at all & the other one, which runs on Saturday nights, specifically excludes the 2 databases listed in the error log.
    – Marvel
    May 1 '17 at 19:50
0

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.