Need TSQL help to monitor the scheduled jobs in SQL 2012


  • I have a request to monitor couple of scheduled SQL jobs in 2012 version.

    Example: There is a SQL job which is scheduled 6 times a day starting 12:55 AM PST. Now for what ever reason, if this job fails or if it is an out of cycle run. Because, we have few scenarios where we need to manually kick off the jobs apart from schedule. I need to differentiate between manually kicked off runs and Scheduled runs of that job.

    I checked EXEC msdb.dbo.sp_help_jobactivity but is only giving me requested date and starttime info.

    I need prepare a script to dynamically check and differentiate between manually kicked off runs and Scheduled runs of SQL jobs.

    • Edited by Vinnydrums Tuesday, October 08, 2013 10:27 AM
    Tuesday, October 08, 2013 10:26 AM


  • May be, this would help you:

    Select A.job_id,, 
    Left(Replace(last_outcome_message,'The job succeeded.  The Job was invoked by',''),
    Charindex('.',Replace(last_outcome_message,'The job succeeded.  The Job was invoked by','')))
    From msdb.dbo.SysJobServers A
    Inner Join msdb.dbo.sysjobs B on A.job_id = B.job_id

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, October 08, 2013 11:19 AM

All replies