Post reply

SQL Agent jobs status while running

  • July 23, 2013 at 2:56 am

    #277776

    Dear all,

    I have around 80 SQL agent jobs.

    and these jobs will run in the duration of 3 hours. i can not go to agent activity monitor and check the job status everytime as i have other work also.

    I want a script when i execute it it should show the real time status of the job.

    I mean job1 executing

    Job2 Completed

    Job3 failed.

    Can you please provide me the solution for this.

    This will be great help.

    Thanks in Advance.

  • July 23, 2013 at 3:29 am

    #1634820

    exec msdb.dbo.sp_help_jobactivity

    if you look in the proc, it's essentially this:

    SELECT

    ja.session_id,

    ja.job_id,

    j.name AS job_name,

    ja.run_requested_date,

    ja.run_requested_source,

    ja.queued_date,

    ja.start_execution_date,

    ja.last_executed_step_id,

    ja.last_executed_step_date,

    ja.stop_execution_date,

    ja.next_scheduled_run_date,

    ja.job_history_id,

    jh.message,

    jh.run_status,

    jh.operator_id_emailed,

    jh.operator_id_netsent,

    jh.operator_id_paged

    FROM

    (msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)

    join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id

    WHERE ja.session_id = (SELECT TOP 1 session_id FROM syssessions ORDER by agent_start_date DESC)

    You should be able to modify that to meet your needs.

  • July 23, 2013 at 3:35 am

    #1634824

    Alternatively why can't you just keep the SQL Agent Monitor open? You can set auto refresh for whatever frequency you would like (i.e. 10 sec, 30 sec, etc.) and add a filter that only show jobs that are currently executing. That way the monitor is running the query for you and you will only see those jobs that are currently running.

    Joie Andrew
    "Since 1982"

  • July 23, 2013 at 3:44 am

    #1634830

    Thanks for the replies.

    I want to run this query as a report(rdl) and make available to other team member as well.:-) so that my seniors should not come to me for every 5 mins asking for status.

  • July 23, 2013 at 3:50 am

    #1634834

    sushilkumar217 (7/23/2013)

    Thanks for the replies.

    I want to run this query as a report(rdl) and make available to other team member as well.:-) so that my seniors should not come to me for every 5 mins asking for status.

    In that case, just use the query Gazareth gave you and put it in a report.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 23, 2013 at 3:54 am

    #1634839

    Yeah gazareth given a superb query.

    Actually my requirement is.

    The jobs status should only show for present day. and the jobs which are currently executing also.Irrespective of the yesterdays run status.

    I have modified the query little bit.

    select J.name as Jobname,ja.start_execution_date as [Last Refresh],

    case

    WHEN jh.run_status IS NULL THEN 'Not Yet started or Executing'

    WHEN jh.run_status = 0 THEN 'Failed'

    WHEN jh.run_status = 1 THEN 'Succeded'

    END as [Current Status]

    from (msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)

    join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id

    WHERE ja.session_id = (SELECT TOP 1 session_id FROM syssessions ORDER by agent_start_date DESC)

    I just want to make this to show the report only for present day report .

    Please help

  • July 23, 2013 at 4:42 am

    #1634860

    You can add a condition to the WHERE clause that checks if start_execution_date is today or not.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 23, 2013 at 4:43 am

    #1634862

    Thanks.

    Adding this to your where clause should show anything still running plus anything that has or is due to run today:

    AND ((ja.start_execution_date is not null and ja.stop_execution_date is null)

    OR (ja.start_execution_date >= CAST(GETDATE() as date) and ja.start_execution_date < CAST(GETDATE()+1 as date)))

  • July 23, 2013 at 8:37 am

    #1634983

    Thank You gazareth.

    Great help indeed

  • July 23, 2013 at 9:45 am

    #1635040

    No problem!

  • August 7, 2013 at 1:16 am

    #1639239

    Hi Sushil,

    Why you will not going with SQL Mail Alerts because when ever job will fail you get a mail and it is very to understand the job name, etc.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply