List running Schedule jobs with elapsed time in SQL Server by serverku

A few months ago, I wrote some articles about SQL Server scheduled jobs and details for same which are listed below

1. When exactly scheduled jobs started and completed
2. Find running scheduled jobs in SQL Server - Alternate way
3. Another way to list out running scheduled jobs

Now I am sharing another one script which belongs to above relevant articles with capture all details of executing scheduled jobs with elapsed time. Below is a script which collects all user scheduled jobs which are running for 30 minutes,

USE msdb
GO

SELECT 
 job.Originating_Server as ServerName, 
 job.Name as JobName, 
 activity.run_requested_Date as StartDate,
 DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) as ElapsedTime_Minute
FROM dbo.sysjobs_view job WITH (NOLOCK)
INNER JOIN dbo.sysjobactivity activity WITH (NOLOCK)
 ON (job.job_id = activity.job_id)
WHERE run_Requested_date is not null and stop_execution_date is null
AND activity.run_requested_Date > DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
AND job.category_id = 0
AND DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) >= 30
GO

I would like you to share other ways to capture the same information. Stay tuned for more.

Another way to list out running scheduled jobs - SQL Server by serverku

We have been discussing the way to get scheduled jobs which are executing. Also wanted to tell you that this way is just similar to an earlier post with the same subject, but this an another alternate way. Please read my earlier relative posts for the same. You may aware of the dbo. sp_help_job which also help us they get the same information which you may read my one of the recent post.

Here I am sharing one more way to get the same information, where need to use sp_get_composite_job_info system object from msdb database and pass @execution_status parameter and value should be 1 for executing state.

USE msdb
GO

EXEC dbo.sp_get_composite_job_info @execution_status=1;

This will output all jobs which are currently executing. But when you run EXEC msdb.dbo.sp_get_composite_job_info without any parameter, it will give all the jobs and status as well. And the state values are following,

0 = Not idle or suspended,
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = WaitingForStepToFinish,
7 = PerformingCompletionActions

You can pass any parameter values to get the jobs having that state. We all know about the query to get enabled\disabled scheduled jobs using db.sysjobs system table and having enabled = 1, but the same information we can know using msdb.dbo.sp_get_composite_job_info as following,

USE msdb
GO

-- Enabled jobs
EXEC dbo.sp_get_composite_job_info @enabled = 1;

-- Disabled jobs
EXEC dbo.sp_get_composite_job_info @enabled = 0; 

There are some other parameters which also can be used with this object like job_id, job_type etc. You may aware of this sp and may be used. Please suggest any other way using this object.