A T-SQL toolkit for SQL Server Agent: list, script, change and clean up jobs in bulk

Clicking through SSMS works for one job. On a server with 80 jobs you need scripts. Everything below runs against msdb, needs sysadmin (or the right SQL Agent role) and is safe to read before you change anything.

#Inventory: every job, owner, schedule state and last result

USE msdb;
SELECT  j.name                          AS job_name,
        j.enabled,
        SUSER_SNAME(j.owner_sid)        AS owner,
        c.name                          AS category,
        (SELECT COUNT(*) FROM dbo.sysjobsteps s WHERE s.job_id = j.job_id) AS steps,
        (SELECT COUNT(*) FROM dbo.sysjobschedules js JOIN dbo.sysschedules sc ON sc.schedule_id = js.schedule_id
          WHERE js.job_id = j.job_id AND sc.enabled = 1)                      AS active_schedules,
        h.last_run,
        CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry'
                          WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END AS last_outcome,
        j.date_modified
FROM dbo.sysjobs j
JOIN dbo.syscategories c ON c.category_id = j.category_id
OUTER APPLY (SELECT TOP (1) dbo.agent_datetime(run_date, run_time) AS last_run, run_status
             FROM dbo.sysjobhistory WHERE job_id = j.job_id AND step_id = 0
             ORDER BY instance_id DESC) h
ORDER BY j.name;

dbo.agent_datetime is an undocumented helper in msdb that turns the integer run_date/run_time into a datetime. It's handy, but if you prefer only documented code, build the value yourself (see the job history post).

#Find jobs that need attention

-- enabled jobs with no enabled schedule (will never run on their own)
SELECT j.name FROM msdb.dbo.sysjobs j
WHERE j.enabled = 1 AND NOT EXISTS (
  SELECT 1 FROM msdb.dbo.sysjobschedules js JOIN msdb.dbo.sysschedules s ON s.schedule_id = js.schedule_id
  WHERE js.job_id = j.job_id AND s.enabled = 1);

-- jobs not owned by sa (see the ownership post)
SELECT name, SUSER_SNAME(owner_sid) AS owner FROM msdb.dbo.sysjobs WHERE owner_sid <> 0x01;

-- jobs with no failure notification at all
SELECT name FROM msdb.dbo.sysjobs
WHERE notify_level_email = 0 AND notify_level_eventlog = 0 AND notify_level_page = 0;

-- jobs that haven't run in 90 days
SELECT j.name, MAX(h.run_date) AS last_run_date FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory h ON h.job_id = j.job_id AND h.step_id = 0
GROUP BY j.name
HAVING MAX(h.run_date) IS NULL OR MAX(h.run_date) < CONVERT(int, CONVERT(char(8), DATEADD(day, -90, GETDATE()), 112));

#Change jobs in bulk

Always use the sp_update_job family, not direct UPDATEs on sysjobs. The procedures also refresh SQL Agent's cache.

-- disable every job in a category (e.g. before a maintenance window)
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'EXEC msdb.dbo.sp_update_job @job_name = N' + QUOTENAME(j.name, '''') + N', @enabled = 0;' + CHAR(10)
FROM msdb.dbo.sysjobs j JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id
WHERE c.name = N'App - Nightly ETL' AND j.enabled = 1;
PRINT @sql;          -- review first
-- EXEC sys.sp_executesql @sql;

-- change owner, category and failure email for one job
EXEC msdb.dbo.sp_update_job @job_name = N'Orders - Load', @owner_login_name = N'sa',
     @category_name = N'App - Nightly ETL', @notify_level_email = 2, @notify_email_operator_name = N'DBA Team';

Generate the statements, PRINT them, read them, then run them. Keep the printed output with your change record so you can reverse it (@enabled = 1).

#Create a job from script

DECLARE @job_id uniqueidentifier;
EXEC msdb.dbo.sp_add_job @job_name = N'DBA - Cycle error log', @owner_login_name = N'sa',
     @category_name = N'Database Maintenance', @notify_level_eventlog = 2,
     @notify_level_email = 2, @notify_email_operator_name = N'DBA Team', @job_id = @job_id OUTPUT;
EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = N'Cycle', @subsystem = N'TSQL',
     @command = N'EXEC sys.sp_cycle_errorlog;', @database_name = N'master', @retry_attempts = 1, @retry_interval = 5;
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id, @name = N'Weekly Sunday 00:00',
     @freq_type = 8, @freq_interval = 1, @freq_recurrence_factor = 1, @active_start_time = 0;
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = N'(local)';

Forgetting sp_add_jobserver is the classic mistake: the job exists but never runs, because it isn't targeted at any server.

#Script out, copy and back up jobs (dbatools)

# one .sql file per job, ready for source control
Get-DbaAgentJob -SqlInstance sql01 | ForEach-Object {
    $_ | Export-DbaScript -FilePath "D:\repo\agent-jobs\sql01\$($_.Name -replace '[\\/:*?""<>|]','_').sql"
}

# copy jobs (and their operators and categories) to another server, without overwriting
Copy-DbaAgentJob -Source sql01 -Destination sql02 -Job 'DBA - Cycle error log'
Copy-DbaAgentOperator -Source sql01 -Destination sql02
Copy-DbaAgentJobCategory -Source sql01 -Destination sql02

Without dbatools, SSMS can script many jobs at once: Object Explorer Details (F7) on the Jobs folder, select the jobs, then Script Job as → CREATE To.

#Delete jobs safely

-- keep a script first, then delete; @delete_unused_schedule removes schedules no other job uses
EXEC msdb.dbo.sp_delete_job @job_name = N'Old - Weekly export', @delete_unused_schedule = 1;

Warning

Jobs created by replication, log shipping, Change Data Capture or maintenance plans belong to those features. Remove them through the feature (for example, removing log shipping or deleting the maintenance plan). Deleting the job directly leaves the feature half-configured.

#Quick reference