Job Monitoring Part -3

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 first visit part 1 and part 2 from here
Job Monitoring Part 1
Job Monitoring Part 2
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,
@date_last_modified DATETIME = NULL,
@description NVARCHAR(512) = NULL, -- We do a LIKE on this so it can include wildcards
@schedule_id INT = NULL -- if supplied only return the jobs that use this schedule

Job based on categoryselect * from msdb..syscategories order by category_idHere you will get info for all category and their name.
Result
category_id category_class category_type name
0 1 1 [Uncategorized (Local)]
1 1 1 Jobs from MSX
2 1 2 [Uncategorized (Multi-Server)]
3 1 1 Database Maintenance
4 1 1 Web Assistant
5 1 1 Full-Text
6 1 1 Log Shipping
7 1 1 Database Engine Tuning Advisor
10 1 1 REPL-Distribution
11 1 1 REPL-Distribution Cleanup
12 1 1 REPL-History Cleanup
13 1 1 REPL-LogReader
14 1 1 REPL-Merge
15 1 1 REPL-Snapshot
16 1 1 REPL-Checkup
17 1 1 REPL-Subscription Cleanup
18 1 1 REPL-Alert Response
19 1 1 REPL-QueueReader
20 2 3 Replication
98 2 3 [Uncategorized]
99 3 3 [Uncategorized]
100 1 1 Report Server
Now if we want to get all jobs related to Database Maintenance we have to use category_id 3
Exec sp_get_composite_job_info @category_id =3
Jobs based on schedule.If we want to find jobs based on certain schedule id
select * from sysschedules
This will give us schedule_id and its related details.
Now we have to pass related schedule_id in the procedure.
Exec sp_get_composite_job_info @schedule_id = 43