Query to find long running jobs in SQL server

Question

select
j.name as 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
h.run_duration,
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
      as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
ON j.job_id = h.job_id 
where j.enabled = 1  
AND 
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) > 1

The above SQL query will fetch list of a all jobs that takes more then a minute. But it give a huge list, i dont want that all. I just want last 2 run of every jobs. I tried using top 2 and order by desc but it does not list all the jobs in the list. I just want last 2 run of every job.

Any suggestions.?

If you can help it, you don't want to do math on something in a searched condition, you prevent the use of indices. Do the math on the `1` instead. — Clockwork-Muse, Jun 24 '14 at 11:35
possible duplicate of [select top 10 records for each category](http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category) — Clockwork-Muse, Jun 24 '14 at 11:36

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

Look at ROW_NUMER() ranging function:

select * from (

select
j.name as 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
h.run_duration,
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
      as 'RunDurationMinutes',
      ROW_NUMBER() OVER(PARTITION BY j.name ORDER BY msdb.dbo.agent_datetime(run_date, run_time) DESC) NROW
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
ON j.job_id = h.job_id 
where j.enabled = 1  
AND 
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) > 1

) t where nrow < 3

To make things clear I have done:

Add new column to your query:

ROW_NUMBER() OVER(PARTITION BY j.name ORDER BY msdb.dbo.agent_datetime(run_date, run_time) DESC) NROW

This column group by all the records by j.name field and number each group by 'RunDateTime' field.

Now we need to get all the records where NROW == 1 or NROW == 2. I have created subquery (not sure it is a best solution) and and WHERE condition

select * from ( ... ) t where nrow < 3

Thanks..But in some cases its returning only the last run just repeating it twice, with same date and time, and not the second last execution. — user3688698, Jun 24 '14 at 09:20
Done. buy using group by condition.But in some cases its returning the job steps also. — user3688698, Jun 25 '14 at 11:58

score 1 · Answer 2 · answered Dec 01 '17 at 13:04

Try this

SELECT
r.session_id,   r.start_time,
TotalElapsedTime_ms = r.total_elapsed_time
,   r.[status]
,   r.command
,   DatabaseName = DB_Name(r.database_id)
,   r.wait_type
,   r.last_wait_type
,   r.wait_resource
,   r.cpu_time
,   r.reads
,   r.writes
,   r.logical_reads
,   t.[text] AS [executing batch]
,   SUBSTRING(
                t.[text], r.statement_start_offset / 2, 
                (   CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) 
                         ELSE r.statement_end_offset 
                    END - r.statement_start_offset ) / 2 
             ) AS [executing statement] 
,   p.query_plan
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY 
    sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY 
    r.total_elapsed_time DESC;

Query to find long running jobs in SQL server

2 Answers2