Tsql query to show currently running sql agent jobs

  • Hi All,

    Using Tsql query I want to know currently running sql agent jobs and how long they have been running in [dd hh:mi:ss]?

    Expected output:

    job name , start time, duration [dd hh:mi:ss]

    I can see it in Job activity monitor for currently running jobs but it is not telling me when it started and how long it has been running.

    job schedule



  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Try sp_whoisactive, you can search for it. It provides all the current running tasks/sql/jobs etc. It will also provide time in days, hours and mins since a task started.


  • Look through the MSDB, everything you need and more is in the system tables in that DB.




  • SELECT j.name AS running_job_name, 
    DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS duration_mins,
    FROM msdb.dbo.sysjobactivity ja
    INNER JOIN msdb.dbo.sysjobs j ON j.job_id = ja.job_id
    WHERE ja.start_execution_date IS NOT NULL AND
    ja.stop_execution_date IS NULL AND
    ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
    ORDER BY duration_mins DESC, job_name

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 5 posts - 1 through 4 (of 4 total)

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