Query to find long running jobs in SQL server -

Query to find long running jobs in SQL server -

select j.name 'jobname', run_date, run_time, msdb.dbo.agent_datetime(run_date, run_time) 'rundatetime', h.run_duration, ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 'rundurationminutes' msdb.dbo.sysjobs j inner bring together msdb.dbo.sysjobhistory h on j.job_id = h.job_id j.enabled = 1 , ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) > 1

the above sql query fetch list of jobs takes more minute. give huge list, dont want all. want lastly 2 run of every jobs. tried using top 2 , order desc not list jobs in list. want lastly 2 run of every job.

any suggestions.?

look @ row_numer() ranging function:

select * ( select j.name 'jobname', run_date, run_time, msdb.dbo.agent_datetime(run_date, run_time) 'rundatetime', h.run_duration, ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 'rundurationminutes', row_number() over(partition j.name order msdb.dbo.agent_datetime(run_date, run_time) desc) nrow msdb.dbo.sysjobs j inner bring together msdb.dbo.sysjobhistory h on j.job_id = h.job_id j.enabled = 1 , ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) > 1 ) t nrow < 3

to create things clear have done:

add new column query:

row_number() over(partition j.name order msdb.dbo.agent_datetime(run_date, run_time) desc) nrow

this column grouping records j.name field , number each grouping 'rundatetime' field.

now need records nrow == 1 or nrow == 2. have created subquery (not sure best solution) , and condition

select * ( ... ) t nrow < 3

sql sql-server sql-server-2008