SQL SERVER – Displaying SQL Agent Jobs Running at a Specific Time

Recently I was troubleshooting at a customer location something that looked trivial. When the customer approached me for a consulting requirement wherein they were saying their system was going unresponsive every day in the morning around a certain time. They were clueless to what is happening and why this was the case almost every day in the week. I got curious to understand what was going wrong with SQL Agent Jobs.

Some of these problems can take a really long time or some of them can be as simple as you think. Here I was clueless to what was the problem. When I got into active discussion with the team, I was curious, there was something they were not telling me. After random troubleshooting with the team and using tools like PerfMon, Profiler etc – I figured out there was a background process running at that time.

SQL SERVER - Displaying SQL Agent Jobs Running at a Specific Time jobsrunning-800x486

I asked the team if there were any Agent jobs that were running at that time. I could see they were clueless and were looking at each other. One developer jumped to put the ball on my court by asking if there is a way to find if there are any methods or script to help them find if any jobs were running. I had to get to my scripts bank that I use and I figured out there was already one handy with me.

Listing SQL Agent Jobs Running at a Specific Time

SELECT * FROM
(
 SELECT JobName, RunStart, DATEADD(second, RunSeconds, RunStart) RunEnd, RunSeconds
 FROM
 (
  SELECT j.name AS 'JobName',
    msdb.dbo.agent_datetime(run_date, run_time) AS 'RunStart',
    ((jh.run_duration/1000000)*86400) 
    + (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600) 
    + (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60) 
    + (jh.run_duration-(jh.run_duration/100)*100) RunSeconds
  FROM msdb.dbo.sysjobs j 
  INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id 
  WHERE jh.step_id=0 --The Summary Step
 ) AS H
) AS H2
WHERE '2016-05-19 10:16:10' BETWEEN RunStart AND RunEnd
ORDER BY JobName, RunEnd

I personally found this handy and the problem was solved as soon as this query ran. They figured out some batch process that was recently deployed and instead of running them at 10PM the administrator had mistakenly scheduled this for 10AM.

This revelation was an eye opener to what one needs to do while doing the configurations. I felt such a simple task can sometimes take ages to solve or a human error can bring the system down so easily. I think I learnt something new and felt this learning can be useful to you too. Do let me know if you find this script about SQL Agent Jobs useful or feel free to extend the same and share via comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL Scripts, SQL Server, SQL Server Agent
Previous Post
PowerShell – Tip: How to Format PowerShell Script Output?
Next Post
SQL SERVER – Fix: Error: Msg 1904 The statistics on table has 65 columns in the key list

Related Posts

4 Comments. Leave new

  • Hi Pinal,

    I am getting no results from this query, why ? Just getting Column names JobName, RunStart etc but no result output

    Please suggest

    Kind Regards

    Braj

    Reply
  • Hi Braj, try below query.

    DECLARE @NO_OF_DAYS INT = 1 –Insert number of days for which result is expected (In this case results for last five days will be shown)

    SELECT * FROM
    (
    SELECT JobName, RunStart, DATEADD(second, RunSeconds, RunStart) RunEnd, RunSeconds
    FROM
    (
    SELECT j.name AS ‘JobName’,
    msdb.dbo.agent_datetime(run_date, run_time) AS ‘RunStart’,
    ((jh.run_duration/1000000)*86400)
    + (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600)
    + (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60)
    + (jh.run_duration-(jh.run_duration/100)*100) RunSeconds
    FROM msdb.dbo.sysjobs j
    INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
    WHERE jh.step_id=0 –The Summary Step
    ) AS H
    ) AS H2
    WHERE RunStart BETWEEN DATEADD(DAY,-(@NO_OF_DAYS),GETDATE()) AND GETDATE()
    ORDER BY JobName, RunEnd

    Reply
  • Just a correction…..Above query will fetch results for last one day

    Reply
  • WHERE RunStart BETWEEN DATEADD(DAY,-(@NO_OF_DAYS),GETDATE()) AND GETDATE()
    AND ‘2022-11-20 05:00’ BETWEEN RunStart AND RunEnd /*added the date to get 7 days worth of data for that time slot */
    ORDER BY JobName, RunEnd

    Reply

Leave a Reply