How to Configure SQL Server Agent Jobs on Windows Server 2022
SQL Server Agent is the built-in job scheduling and automation engine in SQL Server. It runs as a Windows service (SQLSERVERAGENT) and handles routine DBA tasks such as backups, index maintenance, DBCC checks, ETL processing, and alerting. On Windows Server 2022 with SQL Server 2019 or 2022, SQL Server Agent is available in all editions except SQL Server Express (which omits it entirely). This guide covers enabling the service, creating and managing jobs via both T-SQL and the SSMS GUI, and monitoring multi-server environments.
Enabling the SQL Server Agent Service
SQL Server Agent is disabled by default in many fresh SQL Server installations. Enable and start it using PowerShell or SQL Server Configuration Manager.
Via PowerShell (replace MSSQLSERVER with the SQL instance name for named instances):
# Set SQL Server Agent to start automatically and start it now
Set-Service -Name "SQLSERVERAGENT" -StartupType Automatic
Start-Service -Name "SQLSERVERAGENT"
# For a named instance, the service name is SQLAGENT$InstanceName
Set-Service -Name "SQLAGENT$SQL2022" -StartupType Automatic
Start-Service -Name "SQLAGENT$SQL2022"
# Verify the service state
Get-Service -Name "SQLSERVERAGENT" | Select-Object Name, Status, StartTypeVia SQL Server Configuration Manager: expand SQL Server Services, right-click SQL Server Agent, choose Properties, set Start Mode to Automatic, click OK, then right-click again and choose Start.
Confirm the Agent is running from within SQL Server:
-- 1 = running, 0 = not running
SELECT dss.[status], dss.[status_desc]
FROM sys.dm_server_services dss
WHERE dss.servicename LIKE 'SQL Server Agent%';
GOCreating a Job with SSMS GUI
In SSMS Object Explorer, expand SQL Server Agent > Jobs > right-click > New Job. The New Job dialog has five pages:
General: Name the job, optionally assign it to a category (for filtering in Replication Monitor or multi-server views), set Owner (defaults to current login), and enter a description.
Steps: Add one or more steps. Each step has a Type (Transact-SQL, SSIS Package, PowerShell, Operating system (CmdExec), Replication, and others), Run As (a proxy or the Agent service account), On Success/Failure actions (Go to next step, Quit with success, Quit with failure, or jump to a specific step number). This branching capability makes it possible to build complex conditional workflows entirely within a single Agent job.
Schedules: Attach one or more schedules — weekly, daily, hourly, one-time, or on-startup/on-idle. Schedules can be shared across multiple jobs.
Alerts: Associate SQL Agent alerts (defined separately) to this job so that when the job fails it triggers notification.
Notifications: Configure email, net send, or event log actions on job completion, failure, or success. Email notification requires Database Mail to be configured (see the Database Mail article).
Creating a Job with T-SQL
The system stored procedures for SQL Agent jobs live in the msdb database. A complete job creation sequence follows: add the job, add steps, add a schedule, and attach the schedule.
USE msdb;
GO
-- 1. Create the job
EXEC sp_add_job
@job_name = N'Weekly Index Maintenance',
@enabled = 1,
@description = N'Rebuilds and reorganizes fragmented indexes in SalesDB',
@category_name = N'Database Maintenance',
@owner_login_name = N'sa';
GO
-- 2. Add the first step (T-SQL)
EXEC sp_add_jobstep
@job_name = N'Weekly Index Maintenance',
@step_name = N'Rebuild Fragmented Indexes',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1, -- Quit with success
@on_fail_action = 2, -- Quit with failure
@retry_attempts = 1,
@retry_interval = 5, -- minutes
@os_run_priority = 0,
@subsystem = N'TSQL',
@command = N'
DECLARE @db NVARCHAR(128) = DB_NAME();
DECLARE @schema NVARCHAR(128);
DECLARE @table NVARCHAR(128);
DECLARE @index NVARCHAR(128);
DECLARE @frag FLOAT;
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
SELECT s.name, t.name, i.name, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(''SalesDB''), NULL, NULL, NULL, ''LIMITED'') ps
JOIN SalesDB.sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
JOIN SalesDB.sys.tables t ON i.object_id = t.object_id
JOIN SalesDB.sys.schemas s ON t.schema_id = s.schema_id
WHERE ps.avg_fragmentation_in_percent > 5
AND i.index_id > 0;
OPEN cur;
FETCH NEXT FROM cur INTO @schema, @table, @index, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @frag > 30
EXEC (''ALTER INDEX ['' + @index + ''] ON SalesDB.['' + @schema + ''].['' + @table + ''] REBUILD WITH (ONLINE=ON)'');
ELSE
EXEC (''ALTER INDEX ['' + @index + ''] ON SalesDB.['' + @schema + ''].['' + @table + ''] REORGANIZE'');
FETCH NEXT FROM cur INTO @schema, @table, @index, @frag;
END
CLOSE cur;
DEALLOCATE cur;',
@database_name = N'SalesDB';
GO
-- 3. Add a second step (Update Statistics) that runs after step 1
EXEC sp_add_jobstep
@job_name = N'Weekly Index Maintenance',
@step_name = N'Update Statistics',
@step_id = 2,
@on_success_action = 1,
@on_fail_action = 2,
@subsystem = N'TSQL',
@command = N'EXEC sp_updatestats;',
@database_name = N'SalesDB';
GO
-- 4. Create a weekly schedule (every Sunday at 02:00)
EXEC sp_add_schedule
@schedule_name = N'Weekly Sunday 2am',
@enabled = 1,
@freq_type = 8, -- Weekly
@freq_interval = 1, -- Sunday (bitmask: 1=Sun)
@freq_subday_type = 1, -- Once per day
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1, -- Every 1 week
@active_start_date = 20240101,
@active_end_date = 99991231,
@active_start_time = 20000, -- 02:00:00
@active_end_time = 235959;
GO
-- 5. Attach the schedule to the job
EXEC sp_attach_schedule
@job_name = N'Weekly Index Maintenance',
@schedule_name = N'Weekly Sunday 2am';
GO
-- 6. Assign the job to the local server
EXEC sp_add_jobserver
@job_name = N'Weekly Index Maintenance',
@server_name = N'(LOCAL)';
GOAdding a PowerShell Job Step
SQL Server Agent can run PowerShell scripts directly using the PowerShell subsystem. The script executes in the context of the Agent’s PowerShell environment, which includes the SQLPS or SqlServer module:
EXEC sp_add_jobstep
@job_name = N'Weekly Index Maintenance',
@step_name = N'Send Completion Report',
@step_id = 3,
@on_success_action = 1,
@on_fail_action = 2,
@subsystem = N'PowerShell',
@command = N'
$logPath = "C:SQLLogsIndexMaint_" + (Get-Date -Format "yyyyMMdd") + ".log"
$msg = "Index maintenance completed at $(Get-Date)"
Add-Content -Path $logPath -Value $msg
Write-Host $msg
',
@database_name = N'master';
GOAdding a CmdExec Job Step
The CmdExec subsystem runs any Windows command or batch file under the SQL Agent service account (or a proxy account). Avoid running sensitive operations under the Agent service account directly — use a dedicated proxy instead:
EXEC sp_add_jobstep
@job_name = N'Nightly Backup Cleanup',
@step_name = N'Delete Backups Older Than 7 Days',
@step_id = 1,
@subsystem = N'CmdExec',
@command = N'forfiles /p "D:SQLBackups" /s /m *.bak /d -7 /c "cmd /c del @path"',
@on_success_action = 1,
@on_fail_action = 2;
GOManaging Jobs: Starting, Stopping, and Disabling
-- Start a job manually
EXEC msdb.dbo.sp_start_job @job_name = N'Weekly Index Maintenance';
GO
-- Stop a running job
EXEC msdb.dbo.sp_stop_job @job_name = N'Weekly Index Maintenance';
GO
-- Disable a job (prevents scheduled runs but allows manual start)
EXEC msdb.dbo.sp_update_job
@job_name = N'Weekly Index Maintenance',
@enabled = 0;
GO
-- Re-enable
EXEC msdb.dbo.sp_update_job
@job_name = N'Weekly Index Maintenance',
@enabled = 1;
GO
-- Delete a job entirely
EXEC msdb.dbo.sp_delete_job
@job_name = N'Weekly Index Maintenance';
GOMonitoring Job History via sysjobhistory
All job execution history is stored in msdb.dbo.sysjobhistory. Use this query to review recent outcomes:
SELECT TOP 50
j.name AS job_name,
h.step_id,
h.step_name,
CONVERT(DATETIME,
STUFF(STUFF(CAST(h.run_date AS CHAR(8)),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)),6),5,0,':'),3,0,':'))
AS run_datetime,
h.run_duration / 10000 * 3600
+ (h.run_duration / 100 % 100) * 60
+ h.run_duration % 100 AS duration_seconds,
CASE h.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
END AS run_status,
h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
ORDER BY run_datetime DESC;
GOTo check whether any job is currently running:
EXEC msdb.dbo.sp_help_job @execution_status = 1; -- 1 = executing
GOJob Alerts and Notifications
SQL Server Agent alerts fire in response to SQL Server error messages, performance conditions, or WMI events. The most common use is error-number-based alerts that send email when a critical error occurs (severity 17–25). Alerts require Database Mail and an operator to be configured first:
-- Create an operator (DBA email recipient)
EXEC msdb.dbo.sp_add_operator
@name = N'DBA Team',
@enabled = 1,
@email_address = N'[email protected]',
@pager_days = 0;
GO
-- Create an alert for all severity 17+ errors
EXEC msdb.dbo.sp_add_alert
@name = N'Severity 17+ Errors',
@message_id = 0,
@severity = 17,
@enabled = 1,
@delay_between_responses = 60,
@notification_message = N'Critical SQL Server error detected. Check SQL error log immediately.',
@include_event_description_in = 1;
GO
-- Notify the operator when the alert fires
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Severity 17+ Errors',
@operator_name = N'DBA Team',
@notification_method = 1; -- 1=email, 2=pager, 4=net send
GOMulti-Server Administration (MSX/TSX)
In environments with many SQL Server instances, the Master Server (MSX) / Target Server (TSX) architecture lets you define jobs once and deploy them to multiple servers from a central location. The MSX stores job definitions; TSX instances download and execute them, then report results back to the MSX.
Configure the MSX on the central server:
-- On the master server: make it the MSX
EXEC msdb.dbo.sp_msx_defect; -- only if currently enlisted
EXEC msdb.dbo.sp_msx_enlist
@server_name = @@SERVERNAME;
GOOn each target server (run from within that instance):
-- Enlist this server as a TSX pointing at the MSX
EXEC msdb.dbo.sp_msx_enlist
@server_name = N'WIN2022MSX', -- MSX server name
@location = N'DataCenter-1';
GOAfter enlisting targets, create multi-server jobs in SSMS on the MSX: in the New Job dialog, set Target Servers to All Target Servers or specific TSX instances. The Agent on each TSX polls the MSX every 60 seconds (by default) for new instructions and downloads pending jobs automatically.
SQL Server Agent jobs are the backbone of automated database operations on Windows Server 2022. By combining T-SQL steps for database logic, PowerShell for integration tasks, CmdExec for file operations, and SSIS packages for complex ETL, a single Agent job can orchestrate a complete nightly processing pipeline — all schedulable, alertable, and centrally monitored through SSMS and the sysjobhistory table.
