Day-to-day SQL Server Agent job management: the DBA's daily, weekly and monthly routine

SQL Server Agent runs your backups, maintenance and business processes. Most Agent problems aren't hard to fix. They're just noticed late. This routine takes about 10 minutes a day per estate when it's scripted.

#Daily (start of shift)

1. Is Agent running everywhere?

Get-DbaService -ComputerName (Get-Content servers.txt) -Type Agent |
    Where-Object State -ne 'Running' | Select-Object ComputerName, ServiceName, State, StartMode

2. What failed in the last 24 hours?

Get-DbaAgentJobHistory -SqlInstance (Get-Content servers.txt) -StartDate (Get-Date).AddDays(-1) -ExcludeJobSteps |
    Where-Object Status -eq 'Failed' |
    Select-Object SqlInstance, Job, RunDate, Message | Format-Table -Wrap

Then work each failure with the failed-jobs runbook.

3. Is anything still running that shouldn't be?

SELECT j.name, a.start_execution_date, DATEDIFF(minute, a.start_execution_date, GETDATE()) AS running_min
FROM msdb.dbo.sysjobactivity a JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
WHERE a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
  AND a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL
ORDER BY running_min DESC;

Compare each with its usual duration (history analysis post). A backup still running at 9 am is an incident.

4. Are the critical jobs enabled and recent?

SELECT j.name, j.enabled, 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 AND h.run_status = 1
WHERE j.name LIKE N'%Backup%' OR j.name LIKE N'%Integrity%' OR j.name LIKE N'DatabaseBackup%'
GROUP BY j.name, j.enabled ORDER BY j.name;

#Weekly

SELECT name, date_created, date_modified FROM msdb.dbo.sysjobs
WHERE date_modified >= DATEADD(day, -7, GETDATE()) ORDER BY date_modified DESC;

#Monthly

• Test notifications: fail a test job, and confirm the email and the monitoring incident.
• Export jobs to source control and compare with the last export (Export-DbaScript). Unexpected differences are unmanaged changes.
• Schedule report refreshed in the run book; check maintenance windows don't overlap.
• msdb housekeeping: history purge, backup history, mail log; check msdb size.
• Proxy and credential review: are passwords about to expire? Are the accounts still needed?
• Role review: who is in the SQLAgent roles in msdb?
• AG / FCI: are jobs identical on all replicas? (Compare-Object on exported scripts)

# jobs that exist on one AG replica but not the other
$a = Get-DbaAgentJob -SqlInstance sqlag01 | Select-Object -ExpandProperty Name
$b = Get-DbaAgentJob -SqlInstance sqlag02 | Select-Object -ExpandProperty Name
Compare-Object $a $b

#When you make a change

1. Script the current job (Export-DbaScript) before you touch it.
2. Make the change through a script kept in source control.
3. Run the job once (or at the next schedule) and read the history.
4. Update the description, run book and schedule report.

#Build standard for new instances

So the routine has less to find:

• Database Mail + Agent mail profile + fail-safe operator
• Operators for team mailboxes
• Alerts: severity 19–25, errors 823/824/825, AG 1480
• Startup notification job
• Job categories created
• History limits off, with a purge job
• Agent service: Automatic start, auto-restart on
• Standard maintenance jobs (backup, integrity, index and statistics, cleanup), owned by sa, with notifications

# quick compliance view of the build standard across the estate
Get-DbaAgentOperator  -SqlInstance (Get-Content servers.txt) | Select-Object SqlInstance, Name, EmailAddress
Get-DbaAgentAlert     -SqlInstance (Get-Content servers.txt) | Select-Object SqlInstance, Name, IsEnabled
Get-DbaAgentJobCategory -SqlInstance (Get-Content servers.txt) | Where-Object Name -like 'DBA*' | Select-Object SqlInstance, Name

#The SQL Server Agent series

• T-SQL toolkit: list, script, change and clean up jobs
• Production standards for Agent
• Database Mail and the fail-safe operator
• Job failures in the Windows event log
• Alerting when jobs are enabled or disabled
• Reading job and step history
• Eight ways to run a job
• Restart and failover notifications
• Schedule report decoded from msdb
• Custom job categories
• Job ownership and security
• History retention and archiving
• Performance counters and per-job resources
• Proxies and credentials
• SSIS catalog packages from Agent
• Failed-job runbook
• msdb fixed roles

Search for any of these titles from the box on the right, or browse the #sql-agent tag.

Tip

Turn the daily section into one script that emails a single summary per estate: Agent down, failures, long runs and disabled critical jobs. When the summary is empty, the day starts well.