Categorizing sql jobs

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