Custom job categories to organize your SQL Agent jobs

By:   |   Comments (8)   |   Related: > SQL Server Agent


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.


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.

   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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, June 10, 2021 - 3:00:34 PM - giri Back To Top (88841)
i need a query that is how to get the jobs which are running more than 2 hours in sql server

Tuesday, January 15, 2019 - 10:40:37 AM - Able Back To Top (78772)

It seems the table in the article is either outdated or incorrect. syscategories category_id is an id a number, category_class is the one that has the 1,2.3 for job, alert, operator and category_class has the 1,2,3 for local, multi, none.

Monday, July 22, 2013 - 12:41:42 PM - Sudhakar Back To Top (25941)


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



Thursday, January 8, 2009 - 6:32:54 AM - grobido Back To Top (2530)

Try this

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'


Thursday, January 8, 2009 - 4:16:28 AM - Woodsy1978 Back To Top (2528)



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...

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'

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!


Tuesday, June 3, 2008 - 8:52:11 PM - OkinawaDBA Back To Top (1074)

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).

Friday, April 25, 2008 - 10:52:17 AM - jerryhung Back To Top (914)

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 ..."

Thursday, April 24, 2008 - 10:42:57 AM - griffin43 Back To Top (910)
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. 


get free sql tips
agree to terms