How to Configure SQL Server Agent Jobs on Windows Server 2025

SQL Server Agent is a Windows service that executes scheduled administrative tasks — commonly called jobs — on SQL Server instances running on Windows Server 2025. Agent jobs are the backbone of automated database maintenance: they run backups, rebuild indexes, purge old data, execute SSIS packages, and fire alert notifications when errors occur. This tutorial covers enabling and configuring the SQL Server Agent service, creating jobs with T-SQL stored procedures, defining job steps and schedules, setting up alerts, and configuring operators for email notification using Database Mail.

Prerequisites

Windows Server 2025 with SQL Server 2019 or SQL Server 2022 installed
SQL Server Agent service enabled (not available in SQL Server Express edition)
Sysadmin role on the SQL Server instance
Database Mail configured (required for email alert notifications — see the companion tutorial)
A valid SMTP relay or mail server reachable from the Windows Server 2025 host

Step 1: Ensure SQL Server Agent Is Running and Set to Auto-Start

SQL Server Agent must be running before any jobs, alerts, or operators will function. Verify and configure it via PowerShell or Services Manager:

# Check current status of SQL Server Agent
Get-Service -Name SQLSERVERAGENT

# Start the service if stopped
Start-Service -Name SQLSERVERAGENT

# Configure automatic startup so it survives reboots
Set-Service -Name SQLSERVERAGENT -StartupType Automatic

For named instances, the service name follows the pattern SQLAgent$INSTANCENAME:

# For a named instance called PROD
Get-Service -Name "SQLAgent`$PROD"
Set-Service -Name "SQLAgent`$PROD" -StartupType Automatic
Start-Service -Name "SQLAgent`$PROD"

Confirm via T-SQL that the Agent is visible:

SELECT servicename, status_desc, startup_type_desc
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server Agent%';

Step 2: Create a SQL Server Agent Job

Jobs are created in the msdb system database using a series of stored procedures. The following example creates a job that performs a weekly index rebuild on a database named SalesDB:

USE msdb;
GO

-- Step 1: Add the job
EXEC sp_add_job
    @job_name = N'Weekly Index Rebuild - SalesDB',
    @enabled = 1,
    @description = N'Rebuilds all indexes on SalesDB every Sunday at 2 AM',
    @category_name = N'Database Maintenance',
    @owner_login_name = N'sa';
GO

Step 3: Add Job Steps

Each job contains one or more steps. Steps can be T-SQL, PowerShell, SSIS packages, or operating system commands. This step executes a T-SQL index maintenance script:

USE msdb;
GO

EXEC sp_add_jobstep
    @job_name = N'Weekly Index Rebuild - SalesDB',
    @step_name = N'Rebuild All Indexes',
    @step_id = 1,
    @cmdexec_success_code = 0,
    @on_success_action = 1,   -- 1 = Quit with success
    @on_fail_action = 2,       -- 2 = Quit with failure
    @retry_attempts = 2,
    @retry_interval = 5,       -- minutes
    @os_run_priority = 0,
    @subsystem = N'TSQL',
    @command = N'
USE SalesDB;
GO

-- Rebuild indexes with fragmentation above 30%
-- Online rebuild (requires Enterprise edition)
SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent
INTO #FragmentedIndexes
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
  AND ips.index_id > 0;

DECLARE @sql NVARCHAR(MAX) = '''';

SELECT @sql += ''ALTER INDEX ['' + IndexName + ''] ON [dbo].['' + TableName + ''] REBUILD WITH (ONLINE = ON);'' + CHAR(13)
FROM #FragmentedIndexes;

EXEC sp_executesql @sql;
DROP TABLE #FragmentedIndexes;
',
    @database_name = N'SalesDB',
    @flags = 0;
GO

PowerShell Job Step Example

EXEC sp_add_jobstep
    @job_name = N'Weekly Index Rebuild - SalesDB',
    @step_name = N'Log Completion to File',
    @step_id = 2,
    @subsystem = N'PowerShell',
    @command = N'
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$logPath = "C:SQLLogsIndexRebuild.log"
Add-Content -Path $logPath -Value "$timestamp - Index rebuild completed successfully."
',
    @on_success_action = 1,
    @on_fail_action = 2,
    @database_name = N'master';
GO

Operating System (CmdExec) Step Example

EXEC sp_add_jobstep
    @job_name = N'Weekly Index Rebuild - SalesDB',
    @step_name = N'Archive Old Log Files',
    @step_id = 3,
    @subsystem = N'CmdExec',
    @command = N'forfiles /P "C:SQLLogs" /M "*.log" /D -30 /C "cmd /c del @path"',
    @on_success_action = 1,
    @on_fail_action = 2;
GO

Step 4: Configure a Job Schedule

Attach a recurring schedule to run the job automatically. The example below schedules the job every Sunday at 02:00 AM:

USE msdb;
GO

EXEC sp_add_schedule
    @schedule_name = N'Weekly Sunday 2AM',
    @freq_type = 8,              -- 8 = Weekly
    @freq_interval = 1,          -- Sunday (bitmask: 1=Sun, 2=Mon, 4=Tue, 8=Wed, 16=Thu, 32=Fri, 64=Sat)
    @freq_subday_type = 1,       -- 1 = Once per day
    @freq_subday_interval = 0,
    @freq_relative_interval = 0,
    @freq_recurrence_factor = 1, -- Every 1 week
    @active_start_date = 20250101,
    @active_end_date = 99991231,
    @active_start_time = 020000, -- 02:00:00
    @active_end_time = 235959;
GO

-- Attach the schedule to the job
EXEC sp_attach_schedule
    @job_name = N'Weekly Index Rebuild - SalesDB',
    @schedule_name = N'Weekly Sunday 2AM';
GO

One-Time Schedule Example

EXEC sp_add_schedule
    @schedule_name = N'One-Time Migration 2025-06-01',
    @freq_type = 1,              -- 1 = One time
    @active_start_date = 20250601,
    @active_start_time = 030000; -- 03:00 AM
GO

Step 5: Register the Job on the Local Server

USE msdb;
GO

EXEC sp_add_jobserver
    @job_name = N'Weekly Index Rebuild - SalesDB',
    @server_name = @@SERVERNAME;
GO

Step 6: View Job History

After the job runs, inspect the execution history to confirm success or diagnose failures:

USE msdb;
GO

EXEC sp_help_jobhistory
    @job_name = N'Weekly Index Rebuild - SalesDB';
GO

Or query the history tables directly for more control:

SELECT
    j.name AS JobName,
    h.step_name,
    h.run_date,
    h.run_time,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
    END AS RunStatus,
    h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE j.name = 'Weekly Index Rebuild - SalesDB'
ORDER BY h.run_date DESC, h.run_time DESC;

Step 7: Configure Operators for Email Notification

Operators are named recipients (people or distribution lists) that receive notifications when jobs succeed, fail, or are retried. Database Mail must be configured before operators can send email (see the Database Mail tutorial):

USE msdb;
GO

EXEC sp_add_operator
    @name = N'DBA Team',
    @enabled = 1,
    @email_address = N'[email protected]',
    @weekday_pager_start_time = 90000,
    @weekday_pager_end_time = 180000,
    @saturday_pager_start_time = 90000,
    @saturday_pager_end_time = 120000,
    @pager_days = 62;  -- Mon–Sat bitmask
GO

Step 8: Configure Alerts on SQL Server Severity or Error Number

Alerts monitor the SQL Server error log and fire when a specific error number or severity level is detected:

USE msdb;
GO

-- Alert on any Severity 19 (Fatal Resource Errors)
EXEC sp_add_alert
    @name = N'Severity 19 - Fatal Resource Error',
    @message_id = 0,
    @severity = 19,
    @enabled = 1,
    @delay_between_responses = 300,  -- seconds between repeated notifications
    @notification_message = N'A fatal resource error has occurred. Investigate immediately.',
    @include_event_description_in = 1;
GO

-- Link the alert to the DBA Team operator (email notification)
EXEC sp_add_notification
    @alert_name = N'Severity 19 - Fatal Resource Error',
    @operator_name = N'DBA Team',
    @notification_method = 1;  -- 1 = Email
GO

-- Alert on specific error number (e.g., 9002 = Log file full)
EXEC sp_add_alert
    @name = N'Error 9002 - Log File Full',
    @message_id = 9002,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 1;
GO

EXEC sp_add_notification
    @alert_name = N'Error 9002 - Log File Full',
    @operator_name = N'DBA Team',
    @notification_method = 1;
GO

Conclusion

SQL Server Agent jobs on Windows Server 2025 provide the automation backbone for any well-managed SQL Server environment. By combining T-SQL, PowerShell, and CmdExec job steps with flexible recurring or one-time schedules, you can automate nearly every aspect of database maintenance — from index rebuilds and statistics updates to data archival and SSIS package execution. Pairing jobs with alerts and operators ensures your DBA team is notified immediately when critical failures occur, turning SQL Server Agent into a comprehensive monitoring and automation platform. Always verify that the Agent service is set to start automatically and that Database Mail is configured before relying on email notifications in a production environment on Windows Server 2025.

How to Configure SQL Server Agent Jobs on Windows Server 2025