SQL Server Agent: Jobs, Steps, Schedules, Alerts, Operators and Database Mail; Agent on Linux and in Containers

Version note: The T-SQL examples in this post were run on SQL Server 2025 CU10 (17.0.5035.5, Enterprise Developer Edition) in a Linux container against the series' Bookstore lab database. SQL Server Agent is disabled on this lab server; the job definitions, alerts, operators and Database Mail settings were really created and read back from msdb, but running the jobs, their retries and sending e-mail couldn't be run on this server. Their behavior is based on the Microsoft Learn documentation.

Guess First: The Job Is Ready, What Happens at 02:30?

The reporting screen of Bookstore, the online bookstore used as the shared example across this site's series, shows the previous day's sales by category every morning. A stored procedure prepares the numbers, and a SQL Server Agent job that runs every night at 02:30 calls it. I defined the job, attached the schedule, added an operator so the team gets an e-mail if it fails, and started it once by hand to be sure:

EXEC msdb.dbo.sp_start_job @job_name = N'sqls_22_nightly_sales_report';

Will the job run tonight? Will an e-mail go out if it fails? The answer from SQL Server in Docker:

HResult 0x5606, Level 16, State 1
SQLServerAgent is not currently running so it cannot be notified of this action.

SELECT servicename, status_desc, startup_type_desc FROM sys.dm_server_services;
SELECT name, value_in_use FROM sys.configurations WHERE name IN (N'Agent XPs', N'Database Mail XPs');

servicename                     status_desc  startup_type_desc
------------------------------  -----------  -----------------
MSSQLSERVER                     Running      Automatic
SQL Server Agent (MSSQLSERVER)  Stopped      Automatic

name               value_in_use
-----------------  ------------
Agent XPs          0
Database Mail XPs  0

No. The job's definition is in msdb, but the process that runs it isn't up. On SQL Server for Linux, Agent ships in the package but is disabled by default, and so it is in the Docker image. A team that doesn't see this error can look at an empty report table for weeks thinking "we set up the job". In this post we look at Agent's structure of jobs, steps, schedules, alerts and operators, Database Mail, the limits on Linux and in containers, and which work belongs to Agent and which to the application.

Turning Agent On: Windows, Linux and Containers

On Windows, Agent is a separate Windows service (SQLSERVERAGENT); its startup type is chosen at setup and it's started from SQL Server Configuration Manager. Express edition has no Agent. On Linux it's turned on with mssql-conf and SQL Server is restarted; in a container, with an environment variable:

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server

docker run -d --name sql1 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=$env:MSSQL_SA_PASSWORD" -e "MSSQL_PID=Developer" -e "MSSQL_AGENT_ENABLED=true" -p 1433:1433 -v bookstore-sql-data:/var/opt/mssql mcr.microsoft.com/mssql/server:2025-latest

Once Agent is on, the Agent XPs setting becomes 1 as well; SSMS Object Explorer doesn't show the contents of the SQL Server Agent node unless that setting is on. The difference between Agent on Linux and on Windows will matter later in the post: according to Microsoft's SQL Server 2025 on Linux feature page, the CmdExec, PowerShell and SSIS subsystems and alerts aren't supported on Linux. On Linux, job steps are T-SQL and there are no alerts.

Jobs, Steps and Schedules

First the procedure the job will call. It has two rules: it's idempotent (running it twice for the same day gives the same result), and it doesn't pass silently when empty (with no data it throws, the job fails and someone is told):

CREATE TABLE dbo.DailySalesReport
(
    ReportDate date NOT NULL,
    Category varchar(30) NOT NULL,
    Orders int NOT NULL,
    Items int NOT NULL,
    Revenue decimal(14,2) NOT NULL,
    CreatedAt datetime2(0) NOT NULL CONSTRAINT DF_DailySalesReport_CreatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_DailySalesReport PRIMARY KEY (ReportDate, Category)
);
GO
CREATE OR ALTER PROCEDURE dbo.FillDailySalesReport @ReportDate date = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SET @ReportDate = ISNULL(@ReportDate, DATEADD(DAY, -1, CAST(SYSUTCDATETIME() AS date)));
    IF NOT EXISTS (SELECT 1 FROM dbo.Orders WHERE CreatedAt >= @ReportDate AND CreatedAt < DATEADD(DAY, 1, @ReportDate))
        THROW 50001, 'No orders found for the report date; the order import may not have run.', 1;
    BEGIN TRANSACTION;
    DELETE FROM dbo.DailySalesReport WHERE ReportDate = @ReportDate;
    INSERT dbo.DailySalesReport (ReportDate, Category, Orders, Items, Revenue)
    SELECT @ReportDate, b.Category, COUNT(DISTINCT o.OrderId), SUM(i.Quantity), SUM(i.Quantity * i.UnitPrice)
    FROM dbo.Orders AS o
    JOIN dbo.OrderItems AS i ON i.OrderId = o.OrderId
    JOIN dbo.Books AS b ON b.BookId = i.BookId
    WHERE o.CreatedAt >= @ReportDate AND o.CreatedAt < DATEADD(DAY, 1, @ReportDate)
      AND o.Status <> 'Cancelled'
    GROUP BY b.Category;
    PRINT CONCAT('report rows for ', @ReportDate, ': ', @@ROWCOUNT);
    COMMIT;
END;

Before attaching the procedure to the job I tried it by hand. The lab data ends on 20 October 2026, so the call without a parameter, meaning "yesterday", should fail:

EXEC dbo.FillDailySalesReport @ReportDate = '2026-10-19';
EXEC dbo.FillDailySalesReport @ReportDate = '2026-10-19';
SELECT Category, Orders, Items, Revenue FROM dbo.DailySalesReport WHERE ReportDate = '2026-10-19' ORDER BY Category;
EXEC dbo.FillDailySalesReport;

report rows for 2026-10-19: 4
report rows for 2026-10-19: 4
Category  Orders  Items  Revenue
--------  ------  -----  --------
Classic   565     1112   31131.85
Fantasy   146     192    5339.03
Horror    70      85     2554.59
SciFi     382     578    16115.50

Msg 50001, Level 16, State 1, Server sql1, Procedure dbo.FillDailySalesReport, Line 8
No orders found for the report date; the order import may not have run.

The second call didn't duplicate rows, and the third raised an error instead of producing an empty report. The job's only work is to call this procedure; the business rule is in the procedure, and the procedure is in source control. The job itself should also live in source control as a script and be re-runnable:

USE msdb;
GO
IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name = N'sqls_22_nightly_sales_report')
    EXEC dbo.sp_delete_job @job_name = N'sqls_22_nightly_sales_report', @delete_unused_schedule = 1;
EXEC dbo.sp_add_job
    @job_name = N'sqls_22_nightly_sales_report',
    @description = N'Fills dbo.DailySalesReport for yesterday. Source: db/agent/nightly-sales-report.sql',
    @owner_login_name = N'sa';
EXEC dbo.sp_add_jobstep
    @job_name = N'sqls_22_nightly_sales_report',
    @step_name = N'Fill daily sales report',
    @subsystem = N'TSQL',
    @database_name = N'sqls_22',
    @command = N'EXEC dbo.FillDailySalesReport;',
    @retry_attempts = 2,
    @retry_interval = 5,
    @on_success_action = 1,
    @on_fail_action = 2;
EXEC dbo.sp_add_schedule
    @schedule_name = N'sqls_22_daily_0230',
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 023000;
EXEC dbo.sp_attach_schedule @job_name = N'sqls_22_nightly_sales_report', @schedule_name = N'sqls_22_daily_0230';
EXEC dbo.sp_add_jobserver @job_name = N'sqls_22_nightly_sales_report', @server_name = N'(local)';
IF NOT EXISTS (SELECT 1 FROM dbo.sysoperators WHERE name = N'sqls_22_bookstore_dba')
    EXEC dbo.sp_add_operator @name = N'sqls_22_bookstore_dba', @email_address = N'dba-team@example.com';
EXEC dbo.sp_update_job
    @job_name = N'sqls_22_nightly_sales_report',
    @notify_level_email = 2,
    @notify_email_operator_name = N'sqls_22_bookstore_dba';

SQLServerAgent is not currently running so it cannot be notified of this action.
SQLServerAgent is not currently running so it cannot be notified of this action.
SQLServerAgent is not currently running so it cannot be notified of this action.

In the lab, server-level object names start with the sqls_22 prefix; on a real server you'd name the job something like "Bookstore - Nightly sales report". The pieces: a job is a container and has an owner. A step is the command to run; its subsystem (TSQL), the database it runs in, what to do on success and failure (1 quit with success, 2 quit with failure, 3 go to the next step) and the retry setting belong to it. Here, if the step fails it's tried twice more, five minutes apart; useful for a transient error like a deadlock (1205) or a timeout, only a ten-minute delay for a permanent error like "no data". The schedule (freq_type = 4 daily, active_start_time = 023000 02:30:00) is a separate object and can be attached to several jobs. sp_add_jobserver targets the job at this server; a job that misses it never runs. Finally @notify_level_email = 2 e-mails the operator only on failure (1 on success, 3 always). All the settings are in msdb:

SELECT j.name AS job, SUSER_SNAME(j.owner_sid) AS owner, s.step_name, s.subsystem, s.retry_attempts,
       s.retry_interval, sc.name AS schedule, sc.freq_type, sc.active_start_time,
       j.notify_level_email, o.name AS operator
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules AS sc ON sc.schedule_id = js.schedule_id
LEFT JOIN msdb.dbo.sysoperators AS o ON o.id = j.notify_email_operator_id
WHERE j.name = N'sqls_22_nightly_sales_report';

job                           owner  step_name                subsystem  retry_attempts  retry_interval  schedule            freq_type  active_start_time  notify_level_email  operator
----------------------------  -----  -----------------------  ---------  --------------  --------------  ------------------  ---------  -----------------  ------------------  ---------------------
sqls_22_nightly_sales_report  sa     Fill daily sales report  TSQL       2               5               sqls_22_daily_0230  4          23000              2                   sqls_22_bookstore_dba

That active_start_time shows 23000 isn't a mistake: the time is stored as an int in HHMMSS form and the leading zero drops. On a server where Agent runs, the job's history is in msdb.dbo.sysjobhistory; date and time are stored as the same kind of ints, so you read them with msdb.dbo.agent_datetime. run_status values are 0 failed, 1 succeeded, 2 retry, 3 canceled and 4 in progress; the row with step_id = 0 is the job's overall outcome:

SELECT TOP (20) j.name, h.step_id, h.step_name, h.run_status,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_at, h.run_duration, h.retries_attempted, h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE j.name = N'sqls_22_nightly_sales_report'
ORDER BY h.instance_id DESC;

History isn't kept forever: on Windows, by default 1,000 rows in total and 100 per job are kept, and a frequently running job can push last night's failure record out within an hour. Raise that limit when needed, or switch to date-based cleanup with sp_purge_jobhistory.

Permissions: Job Owner, Proxies and msdb Roles

Whose permissions does a T-SQL step run with? If the job owner is sysadmin, with Agent's own account; otherwise on behalf of the owner's login (EXECUTE AS LOGIN). That's why the job owner matters. The most common mistake is leaving the login of the person who created the job as its owner: when that person leaves and their login is deleted or disabled, the jobs stop running, and you usually find out on the first failed night. Use sa or a login that isn't tied to a person and exists only for this purpose. Proxies are only for subsystems other than T-SQL (CmdExec, PowerShell, SSIS) and run the step with a specific credential; since those subsystems don't exist on Linux, neither do proxies. To hand out the right to create and manage jobs without granting sysadmin, msdb has three roles: SQLAgentUserRole (manages its own jobs), SQLAgentReaderRole (sees all jobs and history) and SQLAgentOperatorRole (can start and stop other jobs). We covered the split between logins, users and roles in post 17 of the series.

Alerts, Operators and Database Mail

An operator is a name and an e-mail address. An alert is a rule that notifies an operator when a specific error number, severity or performance condition occurs. The classic example is error 824, which we saw in post 21, and the serious errors with severity 19 to 25:

EXEC msdb.dbo.sp_add_alert
    @name = N'sqls_22_error_824',
    @message_id = 824,
    @delay_between_responses = 600,
    @include_event_description_in = 1;
EXEC msdb.dbo.sp_add_notification
    @alert_name = N'sqls_22_error_824',
    @operator_name = N'sqls_22_bookstore_dba',
    @notification_method = 1;
SELECT name, message_id, severity, enabled, occurrence_count FROM msdb.dbo.sysalerts WHERE name LIKE N'sqls[_]22%';

name               message_id  severity  enabled  occurrence_count
-----------------  ----------  --------  -------  ----------------
sqls_22_error_824  824         0         1        0

The alert was created, but this is a Linux server and alerts aren't supported on Linux. SQL Server doesn't tell you; the definition sits in msdb and will never fire. On Windows, alerts work by watching the Windows application event log and fire only for errors written to it. On Linux the same need is covered by a T-SQL job that reads the error log or an Extended Events session (post 18), or by a monitoring tool outside the server.

Database Mail sends the e-mail. The account (the SMTP server) and the profile are defined in msdb:

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = N'sqls_22_bookstore_alerts',
    @email_address = N'sql-alerts@example.com',
    @display_name = N'Bookstore SQL Server',
    @mailserver_name = N'smtp.example.com',
    @port = 587,
    @enable_ssl = 1;
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = N'sqls_22_bookstore';
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = N'sqls_22_bookstore',
    @account_name = N'sqls_22_bookstore_alerts',
    @sequence_number = 1;
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'sqls_22_bookstore',
    @recipients = N'dba-team@example.com',
    @subject = N'Database Mail test',
    @body = N'Test message from the Bookstore SQL Server.';

HResult 0x3BB1, Level 16, State 1
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', search for 'Database Mail XPs' in SQL Server Books Online.

The account and profile were created; sending was blocked. Database Mail is off by default and is turned on at the server level with sp_configure 'Database Mail XPs', 1; the lab server is shared, so I didn't change that setting. One more detail: Agent has to be told separately which profile to use for job notifications. On Windows that's the Alert System page in the SQL Server Agent properties; on Linux it's mssql-conf set sqlagent.databasemailprofile, and in a container the MSSQL_AGENT_EMAIL_PROFILE environment variable. On a server that skips this, the job fails, the notification step fails silently too, and you find the error in Agent's own log. After setting it up, test with a job that fails on purpose (calling the procedure above with a day that has no data is enough) that the e-mail really arrives.

An Agent Job or a Background Job in the Application?

The first post of the Background Jobs series, "Doing Work Outside the Request", covers the options on the application side (BackgroundService, Hangfire, Quartz.NET). This split decides which belongs where:

Bookstore's report job is a borderline example: the work is entirely inside the database and the data is prepared by a single procedure, so it can stay in Agent. If the report is later rendered as a PDF and e-mailed to customers, that work should move to the application. Azure SQL Database has no Agent; elastic jobs run T-SQL jobs across databases, and Azure SQL Managed Instance offers the whole of Agent.

What's different in SQL Server 2019/2022: the structure of jobs, steps, schedules and operators has been the same for years. SQL Server 2022's contained availability groups carry their own msdb, so jobs travel with the replica on failover; in a classic availability group they don't. SQL Server 2025 added strict encryption support with TDS 8.0 and TLS 1.3 for Agent's connection to the server.

Watch Out in Production

Monitor that Agent is running. The service state (sys.dm_server_services) and each job's last successful run time should be part of a monitoring query; notifications can't tell "the job never failed" from "the job never ran".

Keep jobs in source control as scripts and apply them with the deploy. The script above produces the same job when run twice; a job changed by hand in SSMS is lost at the next server move.

If you use an availability group, jobs are defined on each replica separately and the job's first step should check whether that replica is the primary; otherwise after a failover the report is produced twice or not at all.

Common Pitfalls

Assuming Agent is on in a container. The job definition is created, the schedule is attached, nothing runs.

Spreading business logic across Agent job steps. Code that lives outside source control, tests and code review turns into code nobody dares to change. A step should only call a procedure.

Making a personal login the job owner. When the person leaves, the jobs stop too.

Only defining the notification. If Database Mail XPs is off or Agent wasn't given a profile, no e-mail goes out; test with a job that fails on purpose so you don't find out at the first real error.

Relying on alerts on Linux. The definition is created but never fires.

A Small Experiment

★☆☆ Start your own container with MSSQL_AGENT_ENABLED=true, run the procedure and the job script from this post, start the job with sp_start_job, and read the step_id 0 and 1 rows in sysjobhistory.

★★☆ Change the job so it runs with a date that has no data. How do the retries look in sysjobhistory? What are the retries_attempted and run_status values?

★★★ Set up an SMTP server in your own test environment (for example a test SMTP server running in a container), turn on Database Mail, give Agent a profile, and verify that the failed job's e-mail arrives. What do msdb.dbo.sysmail_allitems and sysmail_event_log show?

Some of our nightly jobs move data: price lists from publishers, databases copied to the development environment. In the next post we'll look at moving data with BULK INSERT, bcp, OPENROWSET and BACPAC, by measuring.