Long running sql jobs

Question

I'd like to know or need to find out more about the long running jobs (anything over a couple of minutes)
Is there a way to use it to generate a list of stats from the various procs in a day?

Answer 1

Hi @Jinal Contractor ,

The following script can return the execution time of the sql jobs. For jobs that are still running incomplete, the column Duration is displayed as null.

SELECT sj.name,   
 sja.run_requested_date,   
 CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration  
FROM msdb.dbo.sysjobactivity sja  
INNER JOIN msdb.dbo.sysjobs sj  
ON sja.job_id = sj.job_id  
WHERE sja.run_requested_date IS NOT NULL  
ORDER BY sja.run_requested_date desc;  

You can use SSMS>SQL Server Agent>Reports to find some details on top jobs(slowest jobs,etc).

Answer 2

(anything over a couple of minutes)

Who told you to check jobs anything running over minute ?. Even if you do get a query what purpose will it server. Please first define a correct approach and what you qualify as long running job

Is there a way to use it to generate a list of stats from the various procs in a day?

Sorry did not get this part of your question. Please explain

Answer 3

I hope you came right. If you are in SQL, this may help you

https://www.executiveplacements.com/Jobs/sql-developer-jobs.asp