Treat SQL Server Agent like production code: standards for naming, ownership, logging, alerting and change

Most production SQL Servers have dozens of jobs written by different people over years. Some are owned by people who left, some fail silently every night, and nobody knows which ones matter. A short, enforced standard fixes this.

#1. Naming and categories

• Name jobs as Owner area - Purpose - Detail: for example DBA - Backup - User databases FULL or Finance - ETL - Load GL.
• Use custom categories per team or application (see the categories post), so reports and bulk changes are easy.
• Put the purpose, owning team and contact in the job description.
• Name steps for what they do (Load staging, Merge into dw), not Step 1.

#2. Ownership and security

• Job owner is sa (or a dedicated, non-personal login). Never a person's login (see the ownership post).
• T-SQL steps owned by sysadmin run in the Agent service context. Keep the Agent service account least-privileged: a virtual account, gMSA or domain account with only what jobs need.
• CmdExec, PowerShell and SSIS steps that touch files or other servers run under a proxy with exactly the rights needed (see the proxies post).
• People who only need to run or monitor jobs get SQL Agent fixed roles in msdb, not sysadmin.

#3. Step design

• One job, one purpose. Don't chain unrelated tasks.
• Set retry attempts and intervals for steps that can hit transient errors (network, locks), typically 2 retries 5 minutes apart.
• Set on success / on failure actions explicitly. "Quit reporting failure" is the safe default for a failed step.
• Make steps idempotent, so a rerun after a failure doesn't duplicate data.
• Send step output to an output file or "Log to table" when the history message (which is truncated) isn't enough.
• Use SET XACT_ABORT ON and THROW in T-SQL steps, so errors fail the step instead of being swallowed.

-- step output to a file (one file per job, appended)
EXEC msdb.dbo.sp_update_jobstep @job_name = N'Finance - ETL - Load GL', @step_id = 1,
     @output_file_name = N'L:\AgentLogs\Finance_ETL_Load_GL_$(ESCAPE_SQUOTE(STRTDT)).txt', @flags = 2;  -- 2 = append

#4. Notifications and alerts

• Database Mail configured and enabled for SQL Agent, with a fail-safe operator.
• Every job notifies an operator (a team mailbox) on failure, and writes to the Windows event log on failure for your monitoring tool.
• Agent alerts for severity 17–25 and errors 823, 824 and 825, plus AG role changes (1480) if you use availability groups.
• A startup notification job that emails when SQL Server or Agent restarts or fails over.
• An alert when jobs are enabled or disabled outside a change.

#5. History and reporting

• Agent history limits raised from the defaults (1,000 rows total, 100 per job), or limits off with a scheduled purge.
• A weekly report: failed jobs, long-running jobs (duration versus the 30-day average) and disabled jobs.
• A schedule report kept with the run book, so on-call staff know what runs when.

#6. Change management

• Every job scripted into source control (dbatools Export-DbaScript), and deployed from there.
• Changes go through the normal change process. date_modified in msdb.dbo.sysjobs is your audit hint.
• After a change, run the job once (or test in non-prod) and check the history.
• On availability groups, every replica has the job, with a gate step at the start that stops the job quietly when the node isn't primary:

-- step 1 "Primary check": fails on purpose on a secondary
IF sys.fn_hadr_is_primary_replica(N'FinanceDW') <> 1
    THROW 50001, 'Not the primary replica for FinanceDW - skipping.', 1;

-- on failure: quit reporting success (1); on success: go to the next step (3)
EXEC msdb.dbo.sp_update_jobstep @job_name = N'Finance - ETL - Load GL', @step_id = 1,
     @on_success_action = 3, @on_fail_action = 1;

On the secondary, the job ends as "succeeded" without doing any work. On the primary, it carries on to the real steps.

#7. Monitor the monitor

• Alert if the SQL Server Agent service is stopped. A stopped Agent means no jobs and no alerts.
• Set the Agent service to Automatic start, with auto-restart enabled in Agent properties.
• Watch msdb size and growth (history, backup history, Database Mail logs).

-- is Agent running?
SELECT servicename, status_desc, startup_type_desc, service_account FROM sys.dm_server_services WHERE servicename LIKE N'SQL Server Agent%';

#The one-page review

Tip

Turn the review table into a weekly SQL Agent job that emails the DBA team only when something breaks the standard. Once the list is empty, keep it empty.