SQL Server Agent Jobs

SQL Server Agent

Simple explanation

SQL Server Agent is your database’s alarm clock and task runner.

You tell it: “Every Sunday at 2 AM, rebuild all indexes.” It wakes up, runs the task, and sends you an email if something goes wrong. It’s been around since SQL Server 2000 and every DBA knows it.

Available on Managed Instance and SQL on VMs — NOT on Azure SQL Database.

Job components

Component	Purpose
Job	A collection of steps that execute sequentially or conditionally
Step	A single task (T-SQL, PowerShell, SSIS package, OS command)
Schedule	When to run (daily, weekly, monthly, on-demand, at startup)
Alert	Responds to SQL Server events (errors, performance conditions)
Operator	Who to notify (email, pager — email is the standard)
Notification	What to send when a job succeeds, fails, or completes

Creating maintenance jobs

Kenji’s standard maintenance jobs on NorthStar MI:

Index maintenance job

-- Create a job for weekly index maintenance
EXEC msdb.dbo.sp_add_job @job_name = 'Weekly Index Maintenance',
    @description = 'Rebuild fragmented indexes on all user databases';

-- Add a step
EXEC msdb.dbo.sp_add_jobstep @job_name = 'Weekly Index Maintenance',
    @step_name = 'Rebuild Indexes',
    @subsystem = 'TSQL',
    @command = N'
DECLARE @sql NVARCHAR(MAX) = '''';
SELECT @sql += ''ALTER INDEX '' + i.name + '' ON '' + SCHEMA_NAME(o.schema_id) + ''.'' + o.name + '' REBUILD;''
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
JOIN sys.objects o ON i.object_id = o.object_id
WHERE ips.avg_fragmentation_in_percent > 30 AND ips.page_count > 1000 AND i.name IS NOT NULL;
EXEC sp_executesql @sql;';

-- Schedule: every Sunday at 2 AM
EXEC msdb.dbo.sp_add_jobschedule @job_name = 'Weekly Index Maintenance',
    @name = 'Sunday 2AM',
    @freq_type = 8,  -- weekly
    @freq_interval = 1,  -- Sunday
    @active_start_time = 020000;

-- Enable the job
EXEC msdb.dbo.sp_update_job @job_name = 'Weekly Index Maintenance', @enabled = 1;

Standard maintenance schedule

Job	Frequency	Time	Purpose
Index rebuild	Weekly (Sunday)	2 AM	Defragment indexes over 30%
Statistics update	Daily	1 AM	Full scan on critical tables
DBCC CHECKDB	Weekly (Saturday)	3 AM	Integrity verification
Backup (full)	Daily	11 PM	Full database backup
Backup (log)	Every 15 min	—	Transaction log backup
Cleanup old data	Monthly	4 AM	Archive/purge per retention policy

Alerts and notifications

Configuring Database Mail (required for email notifications)

-- Enable Database Mail (MI supports this natively)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE;

-- Create a mail profile and account
EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'NorthStar DBA',
    @email_address = 'dba@northstar.com',
    @mailserver_name = 'smtp.northstar.com';

EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = 'DBA Alerts';
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'DBA Alerts',
    @account_name = 'NorthStar DBA', @sequence_number = 1;

Creating an operator and notification

-- Create an operator
EXEC msdb.dbo.sp_add_operator @name = 'DBA Team',
    @email_address = 'dba-team@northstar.com';

-- Add notification to the job
EXEC msdb.dbo.sp_update_job @job_name = 'Weekly Index Maintenance',
    @notify_level_email = 2,  -- on failure
    @notify_email_operator_name = 'DBA Team';

Creating alerts

-- Alert on severity 17+ errors (resource issues)
EXEC msdb.dbo.sp_add_alert @name = 'Severity 17 Alert',
    @severity = 17,
    @notification_message = 'Resource error detected on NorthStar MI';

EXEC msdb.dbo.sp_add_notification @alert_name = 'Severity 17 Alert',
    @operator_name = 'DBA Team', @notification_method = 1;  -- email

Troubleshooting Agent jobs

Problem	Where to Check	Fix
Job not running	Job history in SSMS → Agent → Job Activity Monitor	Check schedule, ensure job is enabled
Job fails on a step	Job history → step detail → error message	Fix the T-SQL error, check permissions
Notifications not sent	Database Mail log (`msdb.dbo.sysmail_event_log`)	Verify SMTP settings, check mail queue
Agent not starting (VMs)	Windows Services → SQL Server Agent	Set to Automatic startup, check service account
Job running too long	`sp_help_jobactivity` or Activity Monitor	Investigate blocking, I/O issues, optimise the job

-- View recent job history
SELECT j.name, h.step_name, h.run_status, h.run_date, h.run_time, h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.run_date >= CONVERT(INT, CONVERT(VARCHAR, GETDATE()-7, 112))
ORDER BY h.run_date DESC, h.run_time DESC;

-- Check running jobs
EXEC msdb.dbo.sp_help_jobactivity;

Question

What are the key components of a SQL Server Agent job?

Click or press Enter to reveal answer

Answer

Job (container), Steps (individual tasks — T-SQL, PowerShell, SSIS), Schedule (when to run), Operators (who to notify), Notifications (on success/failure/completion).

Click to flip back

Question

What must be configured before SQL Server Agent can send email notifications?

Click or press Enter to reveal answer

Answer

Database Mail — configure a mail profile and account with SMTP settings. Then set the Agent to use that profile. Enable Database Mail XPs via sp_configure.

Click to flip back

Question

Where do you check why a SQL Server Agent job failed?

Click or press Enter to reveal answer

Answer

Job history: SSMS → SQL Server Agent → Job Activity Monitor → right-click job → View History. Or query msdb.dbo.sysjobhistory for the error message.

Click to flip back

Knowledge Check

Kenji's weekly index maintenance job has been failing every Sunday. The error message says 'permission denied on object sys.dm_db_index_physical_stats.' What should he check?

Next up: Deploy with ARM, Bicep, PowerShell, and CLI — automate database resource deployment with Infrastructure as Code.