Custom job categories to organize your SQL Agent jobs

By: Tim Ford   |   Read Comments (5)   |   Related Tips: More > SQL Server Agent

Problem
Excellent organizational skills are one of the cornerstones to being a successful Database Administrator.  Sometimes the tools that Microsoft provides us with do not give us complete control over the organization of our environment.  Such is the case with organizing SQL Agent Jobs.  We are able to organize and sort jobs by a variety of factors:  Name, Job Status, Last Run Outcome, Category, to name just a few. 

As DBAs with privileged access we have control over each of these job properties - some upon job creation (such as name, enabled, and category); some as a result of our actions (Last Run Outcome for example).  Until SQL 2005 there was a limitation on one of these Agent job properties.  While we could easily change the category of a job to one of the predefined categories within the New Job GUI in SQL Server Enterprise Manager, creating and assigning custom job categories required a small amount of T-SQL execution first.  If you are still one of those DBAs that have not embraced SQL Server Management Studio you will still need to use this approach to organizing SQL Agent Job Categories, however Microsoft has heeded our requests and has provided a GUI alternative in Management Studio.  In this tip we will look at both approaches.

Solution
The data behind SQL Server Agent is stored in the msdb system database.  In SQL 2000 there was a table, dbo.syscategories, that contained metadata pertaining the SQL Agent Job Categories.  In SQL 2005, dbo.syscategories, like most of the other system tables, was converted to a Compatibility View.  Just what is a Compatibility View?  One of the major security enhancements in SQL 2005 involved sheltering the system tables from the end users.  For backwards-compatibility purposes, the system tables were converted to views, exposing the same metadata as their SQL 2000 table counterparts, without exposing any new features in SQL 2005.  These views will still allow any code derived from SQL 2000 system tables to function in SQL 2005.

Column Name	Data Type	Description
category_id	int	ID for the category 1 = Job 2 = Alert 3 = Operator
category_class	int	Type of item within the category 1 = Local 2 = Multiserver 3 = None
category_type	tinyint	Category type
name	sysname	Category name

fig 1. Schema for the msdb.dbo.syscategories table/view:

SQL Server Enterprise Manager:

Creating a custom SQL Agent category requires only that you execute a system stored procedure (msdb.dbo.sp_add_category).  The stored procedure expects 3 parameters:  @class, @type, and @name.

In the following example I will add a new Agent Job category to my instance to assign to all database backup jobs.  If you use maintenance plans to create your backup jobs you'll note that the category assigned to the associated backup job is Database Maintenance.  I prefer to view my backup jobs (both custom and those derived from Maintenance Plans) separately from the other maintenance jobs created on my SQL instances.

IF (SELECT COUNT(*)     FROM msdb.dbo.syscategories     WHERE category_class = 1         AND category_type = 1         AND [name] = N'Database Backup Job') < 1 EXECUTE msdb.dbo.sp_add_category @class = 'JOB', @type = 'LOCAL', @name = 'Database Backup Job'

This approach works whether you're administering a SQL Server 2000 or 2005 instance.  Even though the functionality exists in SQL Server Management Studio I continue to use the T-SQL code exposed above when I run my scripts that configure new instances of SQL after installation.  Speaking of that SSMS GUI approach, let's turn our attention to that enhancement over Enterprise Manager...

SQL Server Management Studio:

In Management Studio, the same process is exposed via a dialog located under the SQL Server Agent Jobs node in the Object Explorer as shown below:

Right-click the highlighted item and you are presented with the Manage Job Categories dialog that allows you to Add or Delete Job Categories and also the ability to view Agent Jobs by category.

No matter which process you use, code or GUI, once the new category is created, you can return to the New Job GUI and select Database Backup Job as the assigned category for any new job you create.

Next Steps

• Create custom categories for your SQL Agent Jobs.  Suggestions are creating custom categories for any product-specific jobs in addition to categories for Database Backups, Log Backups, and Scheduled DTS packages just to get you started.
• Update existing SQL Agent Jobs in your environment with these new categories.
• Review additional tips on SQL system databases and there purposes here.

Last Update: 4/24/2008

About the author

Tim is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Thursday, April 24, 2008 - 10:42:57 AM - griffin43	Read The Tip
This would be really great if Management Studio had an option to display the jobs by category on the main screen.Does it and did I just miss it?Otherwise, it seems to me, the usefulness of the categories is much diminished.

Friday, April 25, 2008 - 10:52:17 AM - jerryhung	Read The Tip
In SSMS, go to SQL Server Agent -> Jobs right click on the "Jobs" folder -> Manage Job Categories   you can create/delete new category there and you can also see # of Jobs Per Category, and see the SQL Jobs if you click "View Job ..."

Tuesday, June 03, 2008 - 8:52:11 PM - OkinawaDBA	Read The Tip
What I would like to see is a way to create folders or extra nodes within the "Job" node that can group jobs together by their categories so that once a category is assigned it immediately goes into the folder (and I don't have to view all jobs at once when I expand the "Job" node).

Thursday, January 08, 2009 - 4:16:28 AM - Woodsy1978

Read The Tip

Hi,   I'm looking to add this to my scripts when we create a new server as the category seems to have been removed in SQL2005 from SQL2000... IF (SELECT COUNT(*)     FROM msdb.dbo.syscategories     WHERE category_class = 1         AND category_type = 1         AND [name] = N'Database Backup Job') < 1 EXECUTE msdb.dbo.sp_add_category @class = 'JOB', @type = 'LOCAL', @name = 'Database Backup' I wanted to add a bit of error handling in case the category has already been added, so it will not make the rest of the script fall over. Can anyone help as i'm having trouble getting the syntax and logic correct, many thanks in advance!  Chris

Thursday, January 08, 2009 - 6:32:54 AM - grobido	Read The Tip
Try this  IF NOT EXISTS (SELECT TOP 1 *                  FROM msdb.dbo.syscategories                 WHERE category_class = 1                   AND category_type = 1                   AND [name] = N'Database Backup Job')        EXECUTE msdb.dbo.sp_add_category @class = 'JOB', @type = 'LOCAL', @name = 'Database Backup'

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen." Get your SQL Server database under version control now! Find out why... Wish your SQL Servers could run wide open? Learn how the Edgewood SQL Server Consultants can make it happen. Join the over million SQL Server Professionals who get their issues resolved daily. Free Learning - Using SQL Server DMVs to Help Improve Performance