SQL Server Agent Jobs, Schedules, Steps, Proxies, Credentials, Alerts, and Failure Handling

Learning outcomes

ServiceHub operations has accumulated a familiar failure mode: a scheduled cleanup is “green” every night, yet the morning queue still contains stale rows. The problem is not that SQL Server Agent failed to launch the job. The job step executed successfully but never verified the business postcondition. Operational automation must therefore be designed as a small program with identity, security context, state transitions, evidence, and failure semantics—not as a calendar entry that happens to run SQL.

1. Agent is an orchestration service around work, not the work itself

A SQL Server Agent job is a named workflow stored in msdb. A job contains one or more steps. A schedule determines when the job starts; schedules can be shared, so changing one shared schedule can affect multiple jobs. An alert reacts to a SQL Server event, performance condition, or WMI event and can start a job or notify an operator. Operators are contact definitions, not security principals.

Each step also has a subsystem and security context. T-SQL steps do not use Agent proxies; their database execution context follows Agent/job-owner rules and can use EXECUTE AS deliberately. CmdExec, PowerShell, SSIS and other subsystem steps can use a proxy. A proxy points to a SQL Server credential, and that credential represents an external identity. That layering matters: putting a password directly into a command line or job step is not “automation”; it is secret sprawl.

USE master;GOIF DB_ID(N'ServiceHubOpsLab') IS NULLBEGIN    CREATE DATABASE ServiceHubOpsLab;END;GOALTER DATABASE ServiceHubOpsLab SET RECOVERY SIMPLE;ALTER DATABASE ServiceHubOpsLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubOpsLab;GOIF SCHEMA_ID(N'lab21') IS NULL EXEC(N'CREATE SCHEMA lab21 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab21.RunAudit', N'U') IS NULLBEGIN  CREATE TABLE lab21.RunAudit  (    run_id bigint IDENTITY PRIMARY KEY,    run_name sysname NOT NULL,    started_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    finished_at datetime2(0) NULL,    outcome varchar(16) NOT NULL DEFAULT 'STARTED',    detail nvarchar(1000) NULL  );END;GOIF OBJECT_ID(N'lab21.WorkQueue', N'U') IS NULLBEGIN  CREATE TABLE lab21.WorkQueue  (    work_id bigint IDENTITY PRIMARY KEY,    status varchar(16) NOT NULL,    created_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    processed_at datetime2(0) NULL,    payload nvarchar(200) NULL  );  INSERT lab21.WorkQueue(status,payload)  SELECT TOP (5000)    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 5 = 0 THEN 'READY' ELSE 'DONE' END,    CONCAT(N'work-',ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))  FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;END;GO

Nothing in the lab requires Agent yet. This is intentional. The operational action must be runnable and testable independently before Agent wraps it. Express learners can execute the action manually or schedule the same script with Windows Task Scheduler, cron/systemd timers, a CI runner, or another approved scheduler; Standard Developer and Enterprise Developer learners can attach it to Agent.

2. A green step can still be an incomplete outcome

The following deliberately weak action updates rows but never checks whether the expected business work existed. If zero rows qualify, SQL Server returns success. An Agent T-SQL step would therefore be green even though the intended “process at least one READY item” contract was not met.

USE ServiceHubOpsLab;GOUPDATE lab21.WorkQueueSET status='DONE', processed_at=SYSUTCDATETIME()WHERE status='READY' AND work_id < 0; -- deliberately matches nothingGOSELECT @@ROWCOUNT AS rows_processed;-- Expected: 0 rows. The UPDATE itself is still a successful SQL statement.

The repair is to encode the postcondition and throw when it is violated. A throw marks the T-SQL batch as failed, which gives Agent or an external scheduler a reliable process-level signal. The audit row also preserves business evidence independently of Agent history retention.

USE ServiceHubOpsLab;GOCREATE OR ALTER PROCEDURE lab21.usp_ProcessReadyBatch  @batch_size int = 25ASBEGIN  SET NOCOUNT ON;  SET XACT_ABORT ON;  IF @batch_size < 1 OR @batch_size > 1000    THROW 51001, 'batch_size must be between 1 and 1000.', 1;  DECLARE @run_id bigint;  INSERT lab21.RunAudit(run_name) VALUES(N'ProcessReadyBatch');  SET @run_id = SCOPE_IDENTITY();  BEGIN TRY    BEGIN TRAN;    ;WITH target AS    (      SELECT TOP (@batch_size) *      FROM lab21.WorkQueue WITH (UPDLOCK, READPAST, ROWLOCK)      WHERE status='READY'      ORDER BY work_id    )    UPDATE target      SET status='DONE', processed_at=SYSUTCDATETIME();    DECLARE @n int = @@ROWCOUNT;    COMMIT;    UPDATE lab21.RunAudit      SET finished_at=SYSUTCDATETIME(), outcome='SUCCEEDED',          detail=CONCAT(N'rows_processed=',@n)    WHERE run_id=@run_id;  END TRY  BEGIN CATCH    IF XACT_STATE() <> 0 ROLLBACK;    UPDATE lab21.RunAudit      SET finished_at=SYSUTCDATETIME(), outcome='FAILED', detail=ERROR_MESSAGE()    WHERE run_id=@run_id;    THROW;  END CATCH;END;GOEXEC lab21.usp_ProcessReadyBatch @batch_size=25;SELECT TOP (5) * FROM lab21.RunAudit ORDER BY run_id DESC;GO

This procedure permits a legitimate no-op: if no READY rows exist, it records zero processed rows rather than pretending something broke. Whether zero is acceptable is a business contract. If your runbook requires at least one row, add that assertion explicitly. Do not hide it in monitoring folklore.

3. Wrap the tested action in Agent only where Agent exists

SQL Server 2025 Express does not include SQL Server Agent. Standard/Standard Developer and Enterprise/Enterprise Developer do. On a qualifying non-production instance, the following creates a disabled-by-default course job first, then enables it after inspection. The job owner is explicit. For a real service account, prefer an ownership and permission model that survives personnel changes; do not make a personal login the hidden production dependency.

USE msdb;GOIF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name=N'ServiceHub - Process Ready Batch')  EXEC dbo.sp_delete_job @job_name=N'ServiceHub - Process Ready Batch';GODECLARE @owner sysname=SUSER_SNAME();EXEC dbo.sp_add_job  @job_name=N'ServiceHub - Process Ready Batch',  @enabled=0,  @owner_login_name=@owner,  @description=N'Chapter 21 disposable job: calls a verified ServiceHubOpsLab procedure.';EXEC dbo.sp_add_jobstep  @job_name=N'ServiceHub - Process Ready Batch',  @step_name=N'Process batch',  @subsystem=N'TSQL',  @database_name=N'ServiceHubOpsLab',  @command=N'EXEC lab21.usp_ProcessReadyBatch @batch_size=25;',  @on_success_action=1,  @on_fail_action=2,  @retry_attempts=0;EXEC dbo.sp_add_schedule  @schedule_name=N'ServiceHub Ch21 Daily 0200',  @freq_type=4, @freq_interval=1, @active_start_time=020000;EXEC dbo.sp_attach_schedule  @job_name=N'ServiceHub - Process Ready Batch',  @schedule_name=N'ServiceHub Ch21 Daily 0200';EXEC dbo.sp_add_jobserver @job_name=N'ServiceHub - Process Ready Batch';GOSELECT j.name,j.enabled,SUSER_SNAME(j.owner_sid) AS owner_name,       s.step_id,s.step_name,s.subsystem,s.database_name,s.retry_attemptsFROM dbo.sysjobs AS jJOIN dbo.sysjobsteps AS s ON s.job_id=j.job_idWHERE j.name=N'ServiceHub - Process Ready Batch';GO

Retries are not automatically safe. If a step sends an email, uploads a file, charges a card, or calls an external API, retrying may duplicate side effects. Retry only when the operation is idempotent or carries a durable deduplication key. A database transaction cannot atomically roll back an arbitrary external system.

For non-T-SQL steps, inspect available subsystems and proxy bindings rather than assuming Windows-only behavior or unrestricted service-account rights. SQL Server on Linux supports Agent but has platform-specific subsystem support; query the installed instance and current documentation.

USE msdb;GOSELECT subsystem, description, max_worker_threadsFROM dbo.syssubsystemsORDER BY subsystem;SELECT proxy_id,name,credential_id,enabledFROM dbo.sysproxiesORDER BY name;SELECT TOP (20)  j.name AS job_name,h.step_id,h.step_name,h.run_status,  h.run_date,h.run_time,h.run_duration,h.messageFROM dbo.sysjobhistory AS hJOIN dbo.sysjobs AS j ON j.job_id=h.job_idORDER BY h.instance_id DESC;GO

Job history is finite and can be purged. Treat it as operational evidence, not an immutable audit trail. For critical jobs, persist structured run evidence in an application/operations table or external log sink and retain it according to policy.

4. Alerts and notifications need an independent delivery path

Agent can respond to SQL Server events, performance conditions, and WMI events. Email notification uses Database Mail and an operator. An alert that fires a job is not proof that an engineer was notified, and an email that was queued is not proof that the receiver saw it. Monitor the notification path itself. Pager and Net Send options are legacy/deprecation-bound and should not be selected for new designs.

5. Production judgment and cleanup

Agent is appropriate when the instance owns the task and the task benefits from SQL-native scheduling, history, alerts, and security boundaries. It is less compelling for estate-wide orchestration, application deployments, cross-system workflows, or environments where a central scheduler already provides stronger identity, secrets management, versioning, and observability. Keep the work in reusable scripts/procedures so the scheduler remains replaceable.

When using Agent, document edition, Agent service state, job owner, step subsystem, database, proxy/credential, schedule time zone assumptions, retry policy, output/history retention, alert path, and postcondition. After failover, jobs that were running can be left with incomplete-looking history and do not simply resume from the interrupted step; design operations to be safely restartable.

Authoritative references

• SQL Server Agent overview
• Create a SQL Server Agent job
• Implement SQL Server Agent security
• Create a SQL Server Agent proxy
• SQL Server 2025 editions and supported features
• SQL Server Agent alerts