solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Custom job categories to organize your SQL Agent jobs

MSSQLTips author Tim Ford By:   |   Read Comments (6)   |   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 
       
AND category_type 
       
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
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server 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 

       
AND category_type 

       
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 

                
AND category_type 

                
AND [name] N'Database Backup Job')

       EXECUTE msdb.dbo.sp_add_category @class 'JOB'@type 'LOCAL'@name 'Database Backup'

 


Monday, July 22, 2013 - 12:41:42 PM - Sudhakar Read The Tip

Hi,

Is there any way to change default job cateogry to custom job category?

 

Thanks.



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



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.