This post is to show you how we can categorize jobs created inside SQL Server agent. In practice, most often you would come across OLAP systems where lot of back end activity will be happening through sql agent jobs that run mostly outside normal business hours. In such cases, its essential to organize them into groups for variety of reasons like below
1. Segregate the high priority jobs from low priority ones for operations/support teams to focus more on jobs based on their priority
2. Scheduling the jobs based on dependency within the same group and between groups so that they run in correct sequence and does processing of data correctly
3. In cases of migration to new server, easy identification of jobs which are dependent so that they can deployed together to new server
In SQL server agent you can categorize jobs by creating job categories. Given below are steps you need to follow for creating job categories
1. Go to SQL Server agent on your sql server connected through management studio,expand it to see jobs node
and right click on it and choose Manage Job Categories
2. The Manage Job Categories pop appears click the add button and in the popup give name for new job category (JobCategory1 in my example) and click ok
3. Go to jobs folder expand it to see the jobs present. Select a job, right click on it and choose properties and in the properties window change the category from [Uncategorized (Local)] (which is the default) to new category we created to assign it to category
For this example I’ve created 5 jobs and assigned even jobs to category 2 and odd jobs to category 1
4. Now go back to Manage job categories window and then select a category and click view jobs to view all the
member jobs. This is an easy way to identify jobs within a category
By doing the above steps we can create category, add jobs to it and also view members jobs within it.
The same steps can also be done programatically
To add a new category use below query
USE [msdb]
EXEC sp_add_category ‘JOB’,’LOCAL’,’JobCategoryname’
To assign a job to category use sp_add_job and pass category_id value generated from syscategories for above category
and to view jobs within category use below query
USE [msdb]
declare @categoryname varchar(100)
set @categoryname=your category name here
select j.[name] as jobname,c.[name] as category_name
from dbo.sysjobs j
inner join dbo.syscategories c
on c.category_id = j.category_id
where c.[name] =@categoryname
