Bring order to 200 Agent jobs with custom job categories

Every job has a category. Most end up as [Uncategorized (Local)]. With a small scheme in place, you can answer "which jobs belong to Finance?" or "disable all ETL jobs for the release" with one query, instead of reading 200 job names.

#Pick a scheme

Keep it short and stable. Two levels in the name work well:

Note

Leave the built-in categories alone (Database Maintenance, REPL-*, Log Shipping, Data Collector, Report Server, and so on). Replication, log shipping, Reporting Services and other features create and look for jobs in these categories.

#Create categories

EXEC msdb.dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = N'DBA - Backup';
EXEC msdb.dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = N'DBA - Maintenance';
EXEC msdb.dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = N'DBA - Monitoring';
EXEC msdb.dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = N'App - Finance';

-- list job categories
SELECT category_id, name, category_class, category_type
FROM msdb.dbo.syscategories WHERE category_class = 1 ORDER BY name;   -- 1 = JOB

@type = 'LOCAL' is for jobs on this server. MULTI-SERVER is for jobs managed from a master server (MSX/TSX).

#Assign jobs in bulk

Assign by naming convention. Generate the statements, review them, then run:

DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'EXEC msdb.dbo.sp_update_job @job_name = N' + QUOTENAME(j.name, '''') + N', @category_name = N'
             + QUOTENAME(CASE WHEN j.name LIKE N'DBA - Backup%'      THEN N'DBA - Backup'
                              WHEN j.name LIKE N'DatabaseBackup%'    THEN N'DBA - Backup'          -- Ola Hallengren
                              WHEN j.name LIKE N'DatabaseIntegrity%' OR j.name LIKE N'IndexOptimize%'
                                   OR j.name LIKE N'%Cleanup%'       THEN N'DBA - Maintenance'
                              WHEN j.name LIKE N'Finance%'           THEN N'App - Finance' END, '''') + N';' + CHAR(10)
FROM msdb.dbo.sysjobs j JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id
WHERE c.name = N'[Uncategorized (Local)]'
  AND (j.name LIKE N'DBA - Backup%' OR j.name LIKE N'DatabaseBackup%' OR j.name LIKE N'DatabaseIntegrity%'
       OR j.name LIKE N'IndexOptimize%' OR j.name LIKE N'%Cleanup%' OR j.name LIKE N'Finance%');
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

For new jobs, set @category_name in sp_add_job, and make it part of your job template.

#Use categories

Report jobs by category:

SELECT c.name AS category, COUNT(*) AS jobs, SUM(CAST(j.enabled AS int)) AS enabled_jobs
FROM msdb.dbo.sysjobs j JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id
GROUP BY c.name ORDER BY c.name;

Failed jobs per category (last 7 days):

SELECT c.name AS category, j.name AS job_name, COUNT(*) AS failures
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id
WHERE h.step_id = 0 AND h.run_status = 0
  AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -7, GETDATE()), 112))
GROUP BY c.name, j.name ORDER BY c.name, failures DESC;

Disable a whole application's jobs for a release, then re-enable:

-- record what was enabled, so you only re-enable those
SELECT j.job_id, j.name INTO #to_restore
FROM msdb.dbo.sysjobs j JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id
WHERE c.name = N'App - Finance' AND j.enabled = 1;

DECLARE @id uniqueidentifier;
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT job_id FROM #to_restore;
OPEN cur; FETCH NEXT FROM cur INTO @id;
WHILE @@FETCH_STATUS = 0 BEGIN EXEC msdb.dbo.sp_update_job @job_id = @id, @enabled = 0; FETCH NEXT FROM cur INTO @id; END
CLOSE cur; DEALLOCATE cur;
-- ...after the release, loop again with @enabled = 1 over the same list (keep it in a real table if the session may end)

Route notifications by category, for example Finance job failures to the Finance support team as well as the DBAs:

EXEC msdb.dbo.sp_add_operator @name = N'Finance Support', @email_address = N'finance-support@corp.example.com';
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'EXEC msdb.dbo.sp_update_job @job_name = N' + QUOTENAME(j.name, '''')
             + N', @notify_level_email = 2, @notify_email_operator_name = N''Finance Support'';' + CHAR(10)
FROM msdb.dbo.sysjobs j JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id WHERE c.name = N'App - Finance';
PRINT @sql;

(A job can email only one operator. Use a distribution list if both teams need the mail.)

#Rename or remove categories

EXEC msdb.dbo.sp_update_category @class = N'JOB', @name = N'App - Finance', @new_name = N'App - Finance & Treasury';
EXEC msdb.dbo.sp_delete_category @class = N'JOB', @name = N'Old category';   -- its jobs move to [Uncategorized (Local)]

#Keep it consistent across servers

Copy-DbaAgentJobCategory -Source sql01 -Destination sql02, sql03
Get-DbaAgentJobCategory -SqlInstance sql01, sql02 | Select-Object SqlInstance, Name, JobCount

Tip

In SSMS, the Job Activity Monitor can filter by category. Once categories are in place, an on-call DBA can see "everything Finance runs tonight" in two clicks.