By: Tim Ford | Comments (8) | Related: > 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.
About the author
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
