SQL Agent job duration in real time
-
Is there a way to get the real time execution period from SQL Agent in a table somewhere? sysjobhistory only stores information about the jobs that have already executed. I'm looking for a way to check if the running jobs are either stuck or are taking longer than usual. I'm looking for the time in the red box below. I've thought about using run_status = 4 (In Progress) as a flag to check for running jobs, but for some reason that flag does not work for the jobs in process.
-
Actually, I figured out how to get around this. The script below tells me all the active jobs that are running.
SELECT j.name, DATEDIFF(second,ja.start_execution_date,GETDATE()) exection_time_seconds FROM msdb..sysjobactivity ja (NOLOCK)
JOIN msdb..sysjobs j (NOLOCK)
ON j.job_id = ja.job_id
WHERE stop_execution_date IS NULL
AND DATEDIFF(second,ja.start_execution_date,GETDATE()) IS NOT null
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply