Job Monitoring Part - 2

In my past article we looked on how to monitor jobs using TSQL using procedure sp_get_composite_job_info
Now we will look some more parameters for it.



Anyone who is reading this article visit other parts from here

Job Monitoring Part 1
Job Monitoring Part3


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">


To get local or Multiserver job
Exec sp_get_composite_job_info @job_type = 'LOCAL'

Exec sp_get_composite_job_info @job_type = 'MULTI-SERVER'

To check all jobs created after a date

here we will use @date_comparator '>'

Exec sp_get_composite_job_info @date_comparator = '>',
@date_created = '2009-01-01'

jobs created before a dateHere we will use @date_comparator '<' Exec sp_get_composite_job_info @date_comparator = '<', @date_created = '2009-01-01' We can use also = and <> also @date_comparator

To check all jobs last modified after a date

here we will use @date_comparator '>'

Exec sp_get_composite_job_info @date_comparator = '>',
@date_last_modified = '2009-01-01'


jobs last modified before a date
Here we will use @date_comparator '<' Exec sp_get_composite_job_info @date_comparator = '<', @date_last_modified = '2009-01-01' We can use also = and <> also @date_comparator