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.