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.
ID for the category
1 = Job
2 = Alert
3 = Operator
Type of item within the category
1 = Local
2 = Multiserver
3 = None
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.
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 Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.
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'
Thursday, January 08, 2009 - 4:16:28 AM - Woodsy1978
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!
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).
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.