Job Monitoring Part - 1 Get execution / enabled /disabled status

As a DBA we we have to regular play with jobs. Monitor jobs is a important activity for any DBA.

Here I am going to discuss an undocumented proc SP_GET_COMPOSITE_JOB_INFO which can be a great help for all of us.



To visit other parts click here.

Job Monitoring Part 2

Job Monitoring Part 3

As we all know that we can monitor jobs by connecting to ther server using management studio -> SQL Server Agent -> Job Activity Monitor
But what if we can do that by using TSQL ?

Here is answer for that
We can find this procedure in MSDB Database.

Syntax
EXEc sp_get_composite_job_info

@job_id UNIQUEIDENTIFIER = NULL,

@job_type VARCHAR(12) = NULL, -- LOCAL or MULTI-SERVER

@owner_login_name sysname = NULL,

@subsystem NVARCHAR(40) = NULL,

@category_id INT = NULL,

@enabled TINYINT = NULL,

@execution_status INT = NULL,

@date_comparator CHAR(1) = NULL, -- >, < or =" @date_created" datetime =" NULL," datetime =" NULL," int =" NULL" style="color: rgb(0, 0, 0);">USE MSDB
Exec sp_get_composite_job_info
Will provide basic info all the jobs on the server.

To find job execution status

So if we want to know which jobs are running right now

Exec sp_get_composite_job_info @execution_status = 1

Which will give info for the jobs which are running now.

Other Value of Execution Status are

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


To find jobs enabled/disabled

If we want to know which jobs are enabled/disabled

Exec sp_get_composite_job_info @enabled = 1 -- For enabled jobs

Exec sp_get_composite_job_info @enabled = 0 -- For disabled jobs

Will provide results for jobs which are enabled/disabled.