Post reply

Tsql query to show currently running sql agent jobs

  • March 25, 2023 at 4:00 pm

    Go to Answer

    #4165005

    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

    Regards,

    Sam

  • March 26, 2023 at 4:10 pm

    #4165193

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

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

  • March 27, 2023 at 5:06 am

    #4165290

    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.

    =======================================================================

  • March 27, 2023 at 8:40 am

    #4165348

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

    sysjobs

    sysjobsteps

    sysjobhistory

  • March 27, 2023 at 2:28 pm

    Answer

    #4165543 

    SELECT j.name AS running_job_name, 
        DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS duration_mins,
        ja.* 
    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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