none
Need TSQL help to monitor the scheduled jobs in SQL 2012

    Question

  • 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

Answers

  • May be, this would help you:

    Select A.job_id, B.name, 
    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

  • use msdb
    go
    select * from  dbo.sysjobhistory

    Ramesh Babu Vavilla MCTS,MSBI

    Tuesday, October 08, 2013 10:29 AM
  • Hello,

    Follow steps mentioned in this link to first create database mail profile and then use this profile to send mail to your Email id when ver job completes or fails

    http://www.orcsweb.com/blog/desiree/how-to-set-up-database-mail-for-sql-server-job-failures/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, October 08, 2013 11:17 AM
  • May be, this would help you:

    Select A.job_id, B.name, 
    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