Home / Databases / SQL Server

DatabasesHow-toBeginner

SQL Server Agent for beginners: jobs, steps, schedules, operators and alerts explained

A first tour of SQL Server Agent for people new to SQL Server operations. What each building block does, how they fit together, and a first useful automated task built end to end.

Deepak Bhardwaj3 min read

Applies toSQL Server 2016–2025 (Standard, Enterprise, Developer), Azure SQL Managed Instance

What SQL Server Agent is

A Windows service that comes with SQL Server and runs scheduled or event-driven work: backups, index maintenance, data loads, health checks and clean-up. Its configuration is stored in the msdb database.

The building blocks

Object What it is Example
Job A named unit of work with an owner and category Maintenance - Index Optimize
Step One action inside a job; runs T-SQL, PowerShell, CmdExec, SSIS and more EXEC dbo.IndexOptimize ...
Schedule When the job runs Sundays 01:00
Operator Who gets told DBA Team (an email address or distribution list)
Alert A reaction to an error number, severity or performance condition Email on severity 17+
Proxy A credential that lets non-T-SQL steps run as a specific Windows account SSIS package that reads a file share

Steps have flow control: on success go to the next step or quit, on failure quit or go to a clean-up step. Each step can also retry a set number of times.

Make sure Agent is running

Get-Service -Name 'SQLSERVERAGENT', 'SQLAgent$*' -ErrorAction SilentlyContinue |
  Select-Object Name, Status, StartType

Set it to Automatic start. In SSMS, Agent appears under the instance in Object Explorer. A red icon means it is stopped.

A first useful job: check database integrity weekly

USE msdb;
EXEC dbo.sp_add_job @job_name = N'Maintenance - CHECKDB',
     @owner_login_name = N'sa', @description = N'Weekly DBCC CHECKDB on all user databases';

EXEC dbo.sp_add_jobstep @job_name = N'Maintenance - CHECKDB', @step_name = N'Run CHECKDB',
     @subsystem = N'TSQL', @database_name = N'master',
     @command = N'EXEC sp_MSforeachdb ''IF DB_ID(''''?'''') > 4 DBCC CHECKDB([?]) WITH NO_INFOMSGS, ALL_ERRORMSGS;''';

EXEC dbo.sp_add_jobschedule @job_name = N'Maintenance - CHECKDB', @name = N'Sun 02:00',
     @freq_type = 8, @freq_interval = 1, @freq_recurrence_factor = 1, @active_start_time = 020000;

EXEC dbo.sp_add_jobserver @job_name = N'Maintenance - CHECKDB';   -- target the local server

sp_MSforeachdb is undocumented and sometimes skips databases. For production, use a maintained solution such as Ola Hallengren's DatabaseIntegrityCheck.

Tell someone when it fails

EXEC msdb.dbo.sp_add_operator @name = N'DBA Team', @email_address = N'dba-team@example.com';
EXEC msdb.dbo.sp_update_job @job_name = N'Maintenance - CHECKDB',
     @notify_level_email = 2, @notify_email_operator_name = N'DBA Team';  -- 2 = on failure

This needs Database Mail set up and enabled for Agent (see the Agent mail profile post).

Typical jobs on a production instance

  • Full, differential and log backups
  • Integrity checks
  • Index and statistics maintenance
  • History and backup-history clean-up (sp_purge_jobhistory, sp_delete_backuphistory)
  • Error log cycling (sp_cycle_errorlog)
  • Application jobs (data loads, reports)

Tip

Name jobs with a prefix by purpose (Backup -, Maintenance -, ETL -) so they sort into groups in SSMS.

Next steps

  • Configure alerts for severity 17–25 and errors 823/824/825.
  • Raise job history retention.
  • Learn the msdb tables, so you can query job state instead of clicking through SSMS.

Related fixes