Domain 4 of 5 · Chapter 1 of 3

Create and manage SQL Server Agent jobs

What a SQL Server Agent job is made of

A job that rebuilds indexes every night at 01:00 and emails the database team when it fails is one SQL Server Agent job, and each part of that sentence maps to one Agent object. SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, called jobs, in SQL Server and Azure SQL Managed Instance[1]. This topic covers scheduling those jobs for regular maintenance, reporting on them with notifications and alerts, and troubleshooting them; automating tasks in Azure SQL Database is covered in Create and manage database tasks in Azure, and deploying database resources with templates and scripts in Automate deployment of database resources.

The objects and the question each one answers

Object Question it answers What it is
Job What runs? A specified series of actions that SQL Server Agent performs, which you can run one or more times and monitor for success or failure[1]
Job step Which action, and as whom? One action the job takes on a database or a server; every job has at least one, and every step runs in a specific security context[2]
Schedule When does it start on its own? The condition or conditions that cause the job to begin running without user interaction[3]
Alert Which event should it react to? An automatic response to a specific event, which can notify operators or run a job[1]
Operator Who hears about it? An alias for a person or group that can receive notification when a job completes or an alert is raised[4]

Keep two definitions in mind. The msdb system database is where Agent keeps these objects: the dbo.sysjobs table in msdb lists the jobs on a server[1], and sp_add_jobstep must be run from the msdb database[5], which is why the Transact-SQL (T-SQL) examples on this page start with USE msdb. The sysadmin fixed server role is the full-control role: members of sysadmin have access to all SQL Server Agent functionality[6], and several rules below apply only to them.

A job does not need a schedule to run: jobs can run according to one or more schedules, in response to one or more alerts, or by executing the sp_start_job stored procedure[1]. SQL Server Management Studio (SSMS) is the graphical tool for all of this; it's the recommended way to create and manage the job infrastructure[5], and each SSMS page named below has a T-SQL stored procedure equivalent.

Read every Agent scenario by first asking which object it is about: the work (job and steps), the start (schedule or alert), the identity a step runs as, or the report (operator and notification).

Which platforms run SQL Server Agent

The platform decides which Agent features exist at all, so settle it before choosing a mechanism. SQL Server Agent runs on SQL Server and Azure SQL Managed Instance, elastic jobs run on Azure SQL Database, and SQL Server Agent is not available in Azure SQL Database[7]; the Azure SQL documentation often shortens the name to SQL Agent, which is the same service. SQL Server on Azure Virtual Machines lets you use full versions of SQL Server in the cloud[8], so an Azure VM running SQL Server has the SQL Server column below.

On Azure SQL Managed Instance, SQL Server Agent is very similar to the SQL Server version[7], with a documented list of exceptions. The rows below are the ones that change answers on this topic; the complete list is in SQL Agent job limitations in SQL Managed Instance[9].

Area SQL Server (including on Azure VMs) Azure SQL Managed Instance
Agent service Disabled at installation unless you explicitly choose to autostart it[1] Always running; enabling or disabling it isn't supported[9]
Agent settings Edited in SQL Server Agent Properties, for example the Alert System page[10] Read-only; sp_set_agent_properties isn't supported[9]
CPU idle schedule Supported[3] Not supported[9]
Alerts SQL Server events, performance conditions, and Windows Management Instrumentation (WMI) events[11] Not supported[9]
Notification methods E-mail, pager, and net send[12] E-mail only; pager and NetSend aren't supported[9]
Proxies Supported[13] Not supported[9]
Job step types T-SQL, CmdExec (operating-system command), PowerShell, replication, Analysis Services, and SQL Server Integration Services (SSIS), among others[5] T-SQL and SSIS steps plus some replication steps; merge replication, Queue Reader, and Analysis Services steps aren't supported[14]. For OS command steps the two Managed Instance articles differ: the job automation article lists OS command/PowerShell steps[9], while the T-SQL differences article says command shell isn't yet supported[14]
Job history retention Default of 1,000 total records and 100 per job[9] Fixed at that default, because Agent properties can't be changed[9]
Database Mail profile for Agent Any profile, selected in Agent Properties[10] Exactly one profile, named AzureManagedInstance_dbmail_profile[14]

The SQL Server documentation writes net send[12] and the Managed Instance documentation writes NetSend[9]; both name the same notification method.

The takeaway: Azure SQL Database has no Agent at all, Managed Instance has Agent without alerts or proxies and with a fixed mail profile name, and SQL Server, including on an Azure VM, has the full feature set.

What can start a job

This section covers the start conditions a job can have; identify the start condition when reading a maintenance requirement. Three routes start a job: a schedule, an alert of any type, or a manual start with the sp_start_job stored procedure[1], or from Job Activity Monitor in SSMS, which can start and stop jobs[15]. A job notification starts nothing: notifications define the rules used to notify operators via email once the job completes[9] (Job notifications report a job's outcome below).

Schedule types

A schedule can be based upon time or an event[3]. The @freq_type parameter of sp_add_schedule names the type:

@freq_type Starts the job Notes
1 Once At a specific date and time, useful for delayed execution of a job[9]
4 Daily Recurring; see Encoding a recurrence with sp_add_schedule below
8 Weekly Recurring
16 Monthly, on a day number Recurring
32 Monthly, relative to @freq_interval (for example the last weekday) Recurring
64 When the SQL Server Agent service starts On Managed Instance, the job is activated after every failover[9]
128 When the computer is idle Not supported in Azure SQL Managed Instance[16]

The codes come from the @freq_type argument of sp_add_schedule[16]. For the CPU idle type, you define the idle condition as a percentage below which CPU usage must remain for a specified time; when CPU usage stays below that percentage for that time, SQL Server Agent starts all jobs that have a CPU idle time schedule[3].

Alerts as the event-driven alternative

As an alternative to job schedules, you can create an alert that responds to an event by running a job[3]. An alert matches an event or a measured condition rather than a calendar, so it fits work that must happen because something occurred, not at a time; the three alert types are covered in SQL Server event alerts, Performance condition alerts, and WMI event alerts below.

Only one execution of a job at a time

Only one instance of a job can run at a time; if you try to run a job manually while it is running as scheduled, SQL Server Agent refuses the request[3]. A manual start therefore cannot create a second, overlapping execution of a job that is still running, and the job's current state, which Current job activity below shows how to read, tells you whether a start is possible.

Pick the start condition first: a calendar rule, Agent start or failover (type 64), and CPU idle (type 128, SQL Server only) are all schedule types, and "when this happens" is an alert.

Schedules are shared objects

A schedule is an object of its own that jobs attach to, not a setting stored inside one job, and that independence is what makes schedule changes reach further than they appear. After a schedule has been created, you can attach it to multiple jobs, even if it was created for a specific job, and you can also detach schedules from jobs[3]; in the other direction, more than one schedule can apply to the same job[1].

The T-SQL pair is sp_add_schedule, which creates the schedule, and sp_attach_schedule, which links it to a job. The documented example creates a NightlyJobs schedule that runs every day at 01:00 and attaches it to both a BackupDatabase job and a RunReports job[16]. The figure draws that example with one addition, a second schedule on RunReports, to show the other direction: more than one schedule can apply to the same job[1].

A change to the schedule is a change to every attached job

Updating a schedule impacts all the jobs attached to it, so to control the schedule of individual jobs you create a separate schedule object for each job[3]. All jobs that use the schedule immediately use the new settings, although changing a schedule doesn't stop jobs that are currently running[17].

Five ways to stop scheduled runs, with different reach

To prevent a scheduled job from running, you disable the schedule, disable the job, detach the schedule from the job, stop the SQL Server Agent service, or delete the schedule[3]:

Action Reach
Disable the schedule (sp_update_schedule with @enabled = 0) Every job that uses the schedule; those jobs can still run in response to an alert or when a user runs them manually[3]
Disable the job That one job
Detach the schedule (sp_detach_schedule) Only that job's association with the schedule; by default (@delete_unused_schedule = 0) the schedule is kept even if no jobs reference it[18]
Stop the SQL Server Agent service Every job the service would run; not possible on Managed Instance, where SQL Server Agent is always running[9]
Delete the schedule Every job that uses the schedule

You must explicitly re-enable a disabled schedule; editing the schedule doesn't re-enable it[3]. And ownership limits who may change what: only members of sysadmin can modify a schedule owned by another user[17], while the job owner can attach a schedule to a job and detach it without being the schedule owner[18]. The one exception for SQLAgentOperatorRole members who enable and disable other owners' schedules is in SQL Server Agent roles in msdb below.

Review the start date after attaching

When you attach a schedule to a job, review the start date the schedule uses to run the job for the first time, because it depends on the day and time you attach it: for a schedule that runs every other Monday at 8:00 A.M., attaching it to a job at 10:00 A.M. on Monday, March 3, 2008 gives a start date of Monday, March 17, 2008, while attaching it to another job on Tuesday, March 4, 2008 gives Monday, March 10, 2008[3]. You can change the schedule start date after you attach the schedule[3], and for weekly or monthly schedules, an @active_start_date in the past is ignored and the current date is used instead[16].

Treat a schedule as a shared resource: edit or disable it only when every attached job should change, and detach it or disable the job when only one job should.

NightlyJobsdaily at 01:00Second scheduleanother ruleBackupDatabasejobRunReportsjob
Schedules and jobs are many-to-many: NightlyJobs starts two jobs, and RunReports also has a second schedule.

Encoding a recurrence with sp_add_schedule

A recurring schedule answers two separate questions, and sp_add_schedule keeps them in separate parameters: @freq_type and @freq_interval choose which days are eligible, @freq_subday_type and @freq_subday_interval choose how often the job starts within each eligible day, and the @active_* parameters bound both. The values below are from the sp_add_schedule reference[16].

Parameter What it controls Values
@freq_interval The days the job runs; its meaning depends on @freq_type Daily (4): every N days. Weekly (8): weekday values combined with OR, Sunday 1, Monday 2, Tuesday 4, Wednesday 8, Thursday 16, Friday 32, Saturday 64. Monthly (16): the day number. Monthly relative (32): 1 to 7 for Sunday to Saturday, 8 day, 9 weekday, 10 weekend day
@freq_subday_type The unit of the within-day repeat 1 at the specified time, 2 seconds, 4 minutes, 8 hours
@freq_subday_interval How many of those units between starts Must be at least 10 seconds; ignored when @freq_subday_type is 1
@freq_relative_interval Which occurrence in the month, for monthly relative only 1 first, 2 second, 4 third, 8 fourth, 16 last
@freq_recurrence_factor Weeks or months between runs Used only when @freq_type is 8, 16, or 32
@active_start_date, @active_end_date The date range, as yyyyMMdd Defaults: today, and 99991231
@active_start_time, @active_end_time The daily time window, as HHmmss on a 24-hour clock Defaults: 000000, and 235959

Do not confuse @freq_interval with the subday interval. Under a daily schedule it means "every N days", not "every N minutes"; minutes between starts belong to @freq_subday_type = 4 with @freq_subday_interval.

Daily, weekly, and monthly relative schedules (sp_add_schedule)

These listings create schedule objects of the kind described in Schedules are shared objects above; each one still needs sp_attach_schedule to start a job.

USE msdb;
GO
-- 1. Every 15 minutes between 01:00 and 05:00, every day
EXECUTE dbo.sp_add_schedule
    @schedule_name = N'Every15MinOvernight',
    @freq_type = 4,              -- daily
    @freq_interval = 1,          -- every 1 day (days, not minutes)
    @freq_subday_type = 4,       -- the repeat unit is minutes
    @freq_subday_interval = 15,  -- 15 of those units between starts
    @active_start_time = 010000, -- HHmmss
    @active_end_time = 050000;
GO
-- 2. Monday, Wednesday, and Friday at 22:00, every week
EXECUTE dbo.sp_add_schedule
    @schedule_name = N'MonWedFri2200',
    @freq_type = 8,               -- weekly
    @freq_interval = 42,          -- Monday 2 + Wednesday 8 + Friday 32
    @freq_recurrence_factor = 1,  -- every 1 week
    @freq_subday_type = 1,        -- once, at the start time
    @active_start_time = 220000;
GO
-- 3. Last weekday of every month at 23:00
EXECUTE dbo.sp_add_schedule
    @schedule_name = N'LastWeekdayMonthly',
    @freq_type = 32,              -- monthly, relative
    @freq_interval = 9,           -- weekday
    @freq_relative_interval = 16, -- last
    @freq_recurrence_factor = 1,  -- every 1 month
    @freq_subday_type = 1,        -- once, at the start time
    @active_start_time = 230000;
GO

In listing 2, @freq_interval = 42 is the sum 2 + 8 + 32 of the Monday, Wednesday, and Friday values. Listing 3 uses the relative type because a fixed monthly schedule (@freq_type = 16) names one day number, and a day number does not track a rule such as "the last weekday", whose date moves from month to month.

Read a recurrence requirement in two passes: which days (type, interval, recurrence factor), then which times on those days (subday type, subday interval, active window).

The Agent email chain

Every Agent email, whether it reports a job outcome or responds to an alert, travels the same chain (an analogy for the account, profile, Agent mail setting, and operator below, not Microsoft terminology), so this section covers the chain once before the next sections cover what triggers a message. Database Mail is the engine component that sends that email: to send notifications to operators by e-mail, you must configure SQL Server Agent to use Database Mail[1], and Database Mail needs access to an e-mail server that supports SMTP (Simple Mail Transfer Protocol)[4].

Account, profile, Agent mail setting, and operator

  1. Database Mail account: the SMTP server details and credentials. On Managed Instance, the documented sample creates it with msdb.dbo.sysmail_add_account_sp, passing the mail server name, user name, and password[9].
  2. Database Mail profile: a named set of accounts. The sample creates the profile with sysmail_add_profile_sp and adds the account with sysmail_add_profileaccount_sp[9].
  3. The Agent mail setting: which profile Agent itself uses. On SQL Server, in SQL Server Agent Properties, select Alert System, select Enable Mail Profile, choose Database Mail in the Mail system list, choose the profile, and restart SQL Server Agent[10]. On Managed Instance those steps aren't necessary, because Agent is already configured to look for a profile called AzureManagedInstance_dbmail_profile[10]; without a profile of that name, Managed Instance is unable to send email through SQL Server Agent[9].
  4. Operator: the recipient. Operators are aliases for people or groups that can receive electronic notification; an operator name must be unique within the instance and can be no longer than 128 characters[4], and an operator doesn't contain security information and doesn't define a security principal; ideally it is an email distribution group rather than one person whose responsibilities can change[9]. You create one in SSMS or with msdb.dbo.sp_add_operator[9].

The profile defines how mail leaves the server and the operator defines who receives it; neither one decides when a message is sent. That decision belongs to a job notification (next section) or an alert response (the alert sections).

Managed Instance network rule

Microsoft recommends an authenticated SMTP relay service, which typically connects through port 25 or 587 for Transport Layer Security (TLS) or port 465 for Secure Sockets Layer (SSL) (Database Mail can use any port), and these ports require a new outbound rule in the managed instance's network security group[9]. A correct profile behind a blocked port still sends nothing; Troubleshooting Database Mail delivery below shows how that failure looks in the Database Mail views.

Pager and net send

The Pager and net send options will be removed from SQL Server Agent in a future version, so avoid them in new development[4], and Managed Instance supports neither[9]. E-mail is the notification method to design for.

The takeaway: account (how to reach the SMTP server), profile (which accounts), Agent mail setting (which profile; a fixed name on Managed Instance), operator (who). The Managed Instance job notifications sample enables Database Mail, creates the account, the AzureManagedInstance_dbmail_profile profile, and an operator, and sets a job notification with sp_update_job[9]; the worked example below follows it.

Job notifications report a job's outcome

A job notification is part of the job itself and fires on the job's final result, so it is the answer whenever a requirement says "tell us when the job fails", "when it succeeds", or "when it finishes". Notifications are part of each SQL Server Agent job, you can send one on job completion, failure, or success, and they depend on an existing operator[19].

Setting it up

In SSMS, open the job's Properties, select the Notifications page, check E-mail, select an operator, and choose When the job succeeds, When the job fails, or When the job completes (regardless of completion status)[12]. In T-SQL the same setting is sp_update_job:

Email an operator only when a job fails (sp_update_job)

This uses the operator and mail chain from The Agent email chain above.

EXEC msdb.dbo.sp_update_job
    @job_name = N'Load data using SSIS',
    @notify_level_email = 2,   -- 1 = on success, 2 = on failure, 3 = on completion
    @notify_email_operator_name = N'AzureSQLTeam';

The level codes are from the Managed Instance job notifications sample[9], which uses this procedure to assign an operator that is emailed when the job completes, fails, or succeeds.

Choosing the level

When the job completes covers both outcomes, so it emails on every run; When the job fails emails only on failure. Most DBAs notify only on failure to avoid an influx of notifications for successful jobs[19]. What counts as "fails" is the result the job quits with, which its step flow decides; How step flow decides the job's outcome below shows how a job can report success after one of its steps failed.

A job notification is not an alert

The two names collide on Microsoft Learn: the T-SQL example on the notify an operator of job status[12] page calls sp_add_notification, which attaches an operator to an existing alert, not to a job. A job-result email needs no alert at all. Creating a severity alert to catch failed jobs is the wrong tool, because an alert matches a logged error number or severity, while a job's failure is the job's own outcome and need not produce one particular logged error.

On Managed Instance, email job notifications work, while pager and NetSend aren't supported[9].

Use the job's Notifications page for anything about the job's own result, and choose the narrowest level that meets the requirement.

SQL Server event alerts

This section and the two after it apply to SQL Server only, including SQL Server on Azure VMs, because alerts aren't supported on Azure SQL Managed Instance[9]. Despite the shared word, these SQL Server Agent alerts are a different feature from Azure Monitor alerts, which notify you when Azure Monitor data indicates there might be a problem[20]; the Managed Instance limitation is one of the SQL Agent job limitations in SQL Managed Instance[9]. Of the three alert types, the SQL Server event alert is the one that reacts to database engine errors.

An event alert can only see what reaches the Windows application log. Events generated by SQL Server are entered into the Windows application log; SQL Server Agent reads the application log and compares the events written there to the alerts you define, and when it finds a match it fires the alert[11]. The figure below traces that path.

Which errors reach the application log

Errors and messages are sent to the Windows application log, and can therefore raise alerts, in these cases: sys.messages errors of severity 19 or higher, any RAISERROR statement invoked WITH LOG, any sys.messages error modified or created with sp_altermessage, and any event logged with xp_logevent[21]. Put the other way, events with severity levels lower than 19 trigger alerts only if you have used sp_altermessage, RAISERROR WITH LOG, or xp_logevent to force them into the Windows application log[22].

The filters

Filter SSMS field sp_add_alert parameter Behavior
Error number Error number @message_id Fires when a specific error occurs, for example error 2571 for unauthorized attempts to run Database Console Commands (DBCC)[11]
Severity Severity @severity (1 through 25) Fires on any error of that severity, for example 15 for T-SQL syntax errors[11]; use either a message ID or a severity, and set the other to 0[21]
Database Database name @database_name Fires only when the event occurs in that database, in addition to the number or severity[11]
Message text Raise alert when message contains @event_description_keyword Fires only when the message contains a string of up to 100 characters[22]; LIKE pattern-matching characters can't be used[21]

The two rules above combine into the most common surprise: the documented severity-15 syntax-error alert matches only syntax errors that are written to the application log, and severity 15 is below 19, so those errors need WITH LOG or sp_altermessage before the alert can fire. The database filter has its own edge: events generated with xp_logevent occur in the master database, so xp_logevent doesn't trigger an alert unless the alert's @database_name is 'master' or NULL[22].

The response

An alert can notify one or more operators or run a job[1]. @delay_between_responses sets a wait, in seconds, between responses, which prevents repeated email when an alert recurs in a short period[21]. A response job suits well-known conditions: an alert on storage errors 823, 824, and 825 can run a job that performs a database consistency check[19].

A severity alert that emails an operator (sp_add_alert, sp_add_notification)

This listing, from the severity-level alert procedure[22], creates the alert and then attaches the email response; it assumes Database Mail and the operator from The Agent email chain exist.

USE msdb;
GO
EXEC dbo.sp_add_alert
    @name = N'Test Alert',
    @message_id = 0,                   -- 0 because the alert uses a severity
    @severity = 23,
    @enabled = 1,
    @include_event_description_in = 1; -- include the error text in the email
GO
EXEC dbo.sp_add_notification
    @alert_name = N'Test Alert',
    @operator_name = N'Alert Operator',
    @notification_method = 1;          -- 1 = e-mail
GO

By default, only members of sysadmin can execute sp_add_alert[21]. When an alert isn't working, check that SQL Server Agent is running, that the event appeared in the Windows application log, and that the alert is enabled[21].

An event alert is right when the requirement names an error number or a severity, and it works only for events that are written to the application log.

Error raised in SQL ServerWritten to theapplication log?NoAlert neversees itYesAgent compares itwith each alertAlert filtersmatch?NoNoresponseYesNotify operatoror run a job
How SQL Server Agent evaluates a SQL Server event alert, from a raised error to the alert response.

Performance condition alerts

A performance condition alert reacts to a measured value instead of an error, which makes it the choice when a requirement is phrased as a threshold ("free space falls below", "wait time rises above"). It applies to SQL Server only, like every alert type, and it has the same response options as an event alert (notify operators, run a job) from SQL Server event alerts above.

The definition

You specify the performance counter to monitor, a threshold, and the behavior the counter must show, on the General page of the New Alert dialog box[11]:

The value is read in the counter's own units. In the documented example, an alert fires when free space in tempdb falls below 1,000 KB: the object is SQLServer:Transactions, the counter is Free space in tempdb (KB), the behavior is falls below, and the value is 1000[11]. In T-SQL, sp_add_alert takes the same definition as @performance_condition, a string of an item (object, counter, or named counter instance), a comparator (>, <, or =), and a numeric value[21]. Typical uses include CPU utilization over 90% for five minutes, or low Page Life Expectancy[19].

Two behaviors to expect

Performance data is sampled periodically, which can lead to a small delay, a few seconds, between the threshold being reached and the alert[11]. The alert can follow the crossing after a short sampling delay.

An Event log variable that stores the server name is limited to 32 characters, so if the combined host name and instance name exceed 32 characters you might get warning 466, "Failed to copy server name … while generating performance counter alerts"[11]. That warning points at the naming limit, not at the alert definition.

Choose a performance condition alert when the requirement is a number compared with a threshold; an error-number alert cannot express "below 1,000 KB" because no single logged error corresponds to it.

WMI event alerts

The third alert type subscribes to an event instead of reading the Windows application log or sampling a counter. Windows Management Instrumentation (WMI) is the Windows event and management interface, and SQL Server Agent can monitor WMI events in addition to SQL Server events and performance conditions[11]; like the other alert types, it is SQL Server only.

The definition

A WMI event alert has two parts:

SQL Server events can be exposed through the WMI Provider for Server Events; the documented example uses the \.\root\Microsoft\SqlServer\ServerEvents namespace and a WQL query that selects ALTER_TABLE events on one table in the AdventureWorks2022 database[23]. In T-SQL these are the @wmi_namespace and @wmi_query parameters of sp_add_alert[21], and by default only sysadmin members can execute sp_add_alert[23].

When to choose it

Use a WMI event alert when the trigger is exposed as a WMI event, such as a schema change selected by a WQL query. A performance condition alert would only compare sampled numbers and could not select that event, and an event alert sees only what is written to the application log.

The three alert types differ only in what they match: an error number or severity in the application log, a counter compared with a threshold, or a WMI event selected by WQL.

Who a job step runs as

Every job step runs as some identity, and when a statement works in your SSMS session but fails inside a job, that identity is the first thing to check. The step type decides the identity: T-SQL steps never use a proxy, and every other step type runs under a proxy or the SQL Server Agent service account. The figure below traces the decision.

T-SQL steps: the job owner, or the service account

T-SQL job steps don't use SQL Server Agent proxies; the step runs as the owner of the job step, or as the SQL Server Agent service account if the owner is a member of the sysadmin fixed server role[2]. Members of sysadmin can also make a T-SQL step run as another user with the @database_user_name parameter of sp_add_jobstep[2]; if the job owner isn't a sysadmin member, that parameter is ignored and the step always runs in the context of the login that owns the job[5]. On Azure SQL Managed Instance, a job that runs T-SQL on a remote server through a linked server needs a login mapping with sp_addlinkedsrvlogin: map the job owner's local login if the owner isn't sysadmin, or map all local logins if it is[24].

The step's database matters as much as its identity. When you create a T-SQL job step, you must identify the database in which to run it[2], and @database_name defaults to NULL, in which case the master database is used[5]. A step whose objects can't be found, although the same statement works interactively, may simply be running in master.

Other step types: a proxy, or the service account

A subsystem is a predefined object that represents functionality available to a job step, such as Operating System (CmdExec), PowerShell, SSIS package execution, Analysis Services, and the replication agents; each proxy has access to one or more subsystems[1]. Because T-SQL job steps don't use proxies, there's no SQL Server Agent subsystem for them[1]. A proxy is a security context in which a job step can run; each proxy corresponds to a security credential[13] that stores a Windows account. If a job step specifies a proxy, it runs in the security context of the proxy's credential; if it doesn't, it runs in the context of the SQL Server Agent service account, and only members of sysadmin can create jobs that don't explicitly specify a proxy[2].

Proxy mechanics that explain failures:

The identity needs the resources, not you

Job steps run in the context of a specific Windows user, which must have the permissions and configuration the step needs: a step that uses a drive letter or a Universal Naming Convention (UNC) path might succeed under your own account while you test it, yet fail when the step's Windows user lacks access to that drive[2]. For CmdExec steps, provide the full path to the executable if it isn't in a directory on the system path or on the path of the user the step runs as[2]. Granting a database role cannot fix a file-share failure, because the share checks the Windows identity of the step, not a database permission. Microsoft's guidelines are to create dedicated accounts for proxies, grant them only the permissions their steps need, and not run the SQL Server Agent service under an account in the Windows Administrators group[24].

On Managed Instance, proxies aren't supported[9] and the instance can't access external resources such as network shares through robocopy[14].

T-SQL step: owner or service account, plus the right database. Any other step: the proxy's credential, or the Agent service account when a sysadmin creates the step without a proxy; either identity needs its own access to every share, file, and executable the step uses.

Is it a T-SQLjob step?YesNoJob owner is asysadmin member?Step specifiesa proxy?YesAgent serviceaccountby defaultNoJob owner'scontextYesProxy'scredentialNoAgent serviceaccountsysadmin only
Which identity a SQL Server Agent job step runs as by default, by step type, job owner, and proxy.

SQL Server Agent roles in msdb

This section covers who may manage Agent objects, which is a separate question from the identity a step runs as (Who a job step runs as above). msdb has three fixed database roles for SQL Server Agent, listed from least to most privileged: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole[6]. Despite the shared word, SQLAgentOperatorRole is unrelated to the operators in The Agent email chain above: an operator doesn't contain security information and doesn't define a security principal[9], while these roles give administrators finer control over access to SQL Server Agent[6]. By default no user is a member of them, membership must be granted explicitly, and sysadmin members don't need them[24]; a user in none of these roles and not in sysadmin doesn't see the SQL Server Agent node in SSMS[6].

The roles are concentric: more privileged roles inherit the permissions of less privileged roles on Agent objects, including alerts, operators, jobs, schedules, and proxies[6]. One consequence is easy to miss: a proxy granted to SQLAgentUserRole is automatically usable by members of SQLAgentReaderRole and SQLAgentOperatorRole[6].

Capability on local jobs and schedules SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole
Create, modify, delete Owned only Owned only Owned only
View jobs, schedules, and their properties Owned only All All
View job history Owned only All All
Start, stop, execute Owned only Owned only All
Enable or disable Owned only Owned only All, with restrictions (below)
Delete job history No, unless granted EXECUTE on sp_purge_jobhistory for owned jobs Same as SQLAgentUserRole Any local job
Change job ownership No No No

The table summarizes the per-role tables in SQL Server Agent fixed database roles[6]. SQLAgentReaderRole adds visibility, not control: it can view all jobs and schedules and their history, but can't change job ownership to gain access to jobs it doesn't own[6], so an on-call administrator who must start and stop other owners' jobs needs SQLAgentOperatorRole.

The SQLAgentOperatorRole restriction

To enable or disable local jobs or schedules they don't own, SQLAgentOperatorRole members must use sp_update_job and sp_update_schedule, specifying only the job or schedule name or identifier and the @enabled parameter; if they specify any other parameter, the procedure fails[6]. The role can switch another owner's job off and on, but cannot edit its definition.

Alerts are a sysadmin boundary

SQLAgentOperatorRole can enumerate alerts and view their properties, but can't create, modify, or delete them[6], and by default only members of sysadmin can execute sp_add_alert[21]. No Agent role by itself lets a user create an alert.

Managed Instance extra grant

On Managed Instance, after you add a user whose login isn't sysadmin to any of the three roles, you must grant that login EXECUTE on master.dbo.xp_sqlagent_enum_jobs, xp_sqlagent_is_starting, and xp_sqlagent_notify; otherwise the user gets error 229, "The EXECUTE permission was denied on the object"[9].

Pick the lowest role that covers the task: own jobs (SQLAgentUserRole), see everyone's jobs (SQLAgentReaderRole), run and pause everyone's jobs (SQLAgentOperatorRole), and sysadmin for alerts, proxies, and editing other owners' jobs.

How step flow decides the job's outcome

A job's result is whatever its step flow quits with, not a tally of whether every step succeeded. Every step has a following step to execute if it succeeds or fails, and a set number of retries if it fails[9], all configured on the step's Advanced page.

Success and failure actions

sp_add_jobstep sets @on_success_action and @on_fail_action with the same four codes[5]:

Code Action Default for
1 Quit with success @on_success_action
2 Quit with failure @on_fail_action
3 Go to next step
4 Go to the step in @on_success_step_id or @on_fail_step_id

With the sp_add_jobstep defaults, a job of several steps quits after its first step. A multistep job therefore sets earlier steps to 3 (go to next step) or 4 (go to a named step).

Retries come first

On the Advanced page, Retry attempts is the number of times, from 0 through 9999, that the step is repeated before it's considered to have failed, and when it is greater than 0, Retry interval is the number of minutes, from 1 through 9999, between attempts[25]. The T-SQL defaults are @retry_attempts = 0, meaning no retries, and @retry_interval = 0[5]. Only after the retries are used up does the step's On failure action run. A bounded retry is the tool for a transient failure; adding a frequent schedule instead would start the whole job again on future schedule events rather than retrying the one step.

A failed step inside a successful job

Suppose a team builds a three-step job: step 1 rebuilds indexes with @on_success_action = 3 (go to next step) and @on_fail_action = 4 with @on_fail_step_id = 3 (go to step 3)[5], step 2 updates statistics, and step 3 logs and cleans up. Step 3 keeps the @on_success_action default, quit with success[5], so even when step 1 fails after its retries, the job's outcome is succeeded if step 3 succeeds, the path the step-flow figure traces (step 2 keeps that default too, so a successful run quits after step 2); a failing step 2 would instead quit with failure, the @on_fail_action default[5]. A When the job fails notification then sends nothing, even though the step-level history shows step 1 failing. Changing the operator does not change that; changing step 3's action to quit with failure does.

CmdExec steps report through an exit code

For an executable program or operating-system command, you must specify the process exit code returned when the command succeeded[2]; @cmdexec_success_code defaults to 0[5]. A program that signals success with another code is recorded as a failed step until that value is set, and the failure then follows the step's routing.

Read a job outcome backwards: final step action, then the route that reached it, then each step's retries and, for CmdExec, its success code.

Step 1: rebuildindexesStep 1 fails afterall retry attempts?NoYesStep 2: updatestatisticsStep 3: log andclean upStep 2succeeds?Step 3succeeds?NoYesYesNoQuit withfailureQuit withsuccessQuit withfailureJob outcome: succeededno failure email
Step flow in a three-step maintenance job: a failed step 1 routes to a cleanup step, and the job still quits with success when step 3 succeeds.

Current job activity

This section covers the first troubleshooting question, "what is happening right now?", which Agent answers from its session records rather than from job history. SQL Server Agent creates a new session each time the service starts and populates the sysjobactivity table in msdb with all the defined jobs; the table preserves the last activity for jobs across Agent restarts, and session information is stored in msdb.dbo.syssessions[15].

Job Activity Monitor

Job Activity Monitor displays sysjobactivity in SSMS; use it to find out which jobs are scheduled to run, the last outcome of jobs that ran during the current session, and which jobs are currently running or idle, and to start and stop jobs, view job properties, and view a job's history[15]. If the SQL Server Agent service fails unexpectedly, the previous session in Job Activity Monitor shows which jobs were in the middle of executing[15]. To open it, expand SQL Server Agent in Object Explorer, right-click Job Activity Monitor, and select View Job Activity[15].

sp_help_jobactivity

sp_help_jobactivity returns a snapshot of the runtime state of jobs at the time the request is processed; without @session_id it reports the most recent session, and without a job name or ID it reports all jobs[26]. Two columns answer common questions:

Column What it tells you
run_requested_source What started the run: 1 a schedule, 2 an alert, 3 startup, 4 a user, 6 a CPU idle schedule[26]
next_scheduled_run_date When the job is next scheduled to run[26]

Only members of sysadmin can view the activity for jobs owned by other users[26].

A job that seems never to end

Jobs running when a SQL Server failover cluster instance fails over don't resume on the new node; they're logged as started, with no further entries for completion or failure, so they appear to never end[1]. An activity record with a start but no stop after a failover can be stale activity from a previous Agent session; check the session and current execution state before treating it as a hung job.

Not in the Azure Monitor activity log

Job steps do not appear in the activity log in the Azure portal: the Azure Monitor activity log records management operations on Azure resources, also called control plane operations[27], such as creating a virtual machine or changing a key vault access policy, not job steps running inside the database engine.

Current state lives in sysjobactivity (Job Activity Monitor, sp_help_jobactivity); finished attempts live in job history, covered next.

Completed job history

This section covers the second troubleshooting question, "what happened on the runs that finished?", which job history answers at both the job and the step level.

In SSMS, expand SQL Server Agent and Jobs, right-click the job, select View History, and read the Log File Viewer; Filter narrows the rows[28]. In T-SQL, sp_help_jobhistory returns the same records:

Failed job and step history entries for one job, with every column (sp_help_jobhistory)

USE msdb;
GO
EXECUTE dbo.sp_help_jobhistory
    @job_name = N'NightlyBackups',
    @run_status = 0,     -- 0 = failed
    @mode = N'FULL';     -- all columns, not the default SUMMARY
GO

The parameters and columns below come from the sp_help_jobhistory reference[29]:

Column Meaning
step_id Step number; 0 marks the row for the job as a whole
sql_message_id, sql_severity For a T-SQL step, the most recent error number and the highest severity it hit
message The job or step history message, up to nvarchar(1024)
run_status 0 failed, 1 succeeded, 2 retry (step only), 3 canceled, 4 in progress, 5 unknown
retries_attempted How many times the step was retried

Reading both kinds of row is what exposes the pattern from How step flow decides the job's outcome: a step row with run_status = 0 under a job row (step_id = 0) with run_status = 1 is a failed step inside a successful job.

Members of SQLAgentUserRole can view the history only for jobs they own[29]. History is also finite: on Managed Instance the retention is fixed at 1,000 total records and a maximum of 100 records per job[9], so a frequently running job can push its own older failures out. When the history message itself is too short, capture the step's output, as the next section describes.

Use job history for completed attempts and step messages, and read the step rows, not just the job row.

Capturing job step output

This section covers step output, which you capture deliberately when a history message is not enough to diagnose a step. SQL Server Agent can write output from some job steps either to an operating system file or to the sysjobstepslogs table in msdb; executable programs and operating system commands, T-SQL statements, and Analysis Services tasks can write to both[2].

Destinations and who may use them

Destination SSMS option (step Advanced page) Behavior
Operating system file Output file Overwritten each time the step runs unless you check Append output to existing file[25]
msdb.dbo.sysjobstepslogs table Log to table Overwritten each time unless you check Append output to existing entry in table[25]
Job history Include step output in history Shown only if there were no errors, and output may be truncated[25]

Only job steps executed by members of sysadmin can write output to operating system files; steps executed by members of SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole can write only to sysjobstepslogs[2]. Job step logs are deleted automatically when their jobs or steps are deleted[2]. In T-SQL, @output_file_name names the file and @flags selects the behavior: 0 overwrite the output file, 2 append to it, 4 write T-SQL step output to step history, 8 log to table and overwrite, 16 log to table and append, 32 write all output to job history[5].

Truncated values from a T-SQL step

T-SQL job steps modify TEXTSIZE, which limits the length of certain columns in result sets, so a SELECT that works as expected in SSMS can return truncated column values when Agent runs it; to avoid this, set TEXTSIZE in the query that the step executes[2]. Longer history retention does not help here: retention controls how long records stay, while the shortening happens inside the step.

Choose the destination by who runs the step (sysadmin for files, the Agent roles for the table), choose append or overwrite on purpose, and set TEXTSIZE in T-SQL steps that return long values.

Troubleshooting Database Mail delivery

When a job ran and its notification never arrived, first check that the notification condition matches the job's outcome; if a message was queued, Database Mail's own status tells you how far it got. The rule to lead with: Database Mail considers mail sent when it's successfully delivered to an SMTP server; later errors, such as an invalid recipient address, can still prevent delivery and won't appear in the Database Mail log[30]. Rerunning the job only repeats its work.

Is Database Mail able to run?

Database Mail requires Service Broker to be enabled for msdb; is_broker_enabled should be 1[30]. The Database Mail XPs option must have a run_value of 1, since Database Mail isn't enabled automatically[30]. The Database Mail external program is activated when there are messages to process; sysmail_help_status_sp shows whether it is started, sysmail_start_sp starts it, and the mail queue from sysmail_help_queue_sp should be in the RECEIVES_OCCURRING state[30]. To send mail, users must be members of DatabaseMailUserRole in msdb and have access to at least one profile[30].

Database Mail health checks (T-SQL)

These queries perform selected Database Mail health checks; they read status only and change nothing.

-- Service Broker enabled for msdb? (should be 1)
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- Is the Database Mail external program started?
EXEC msdb.dbo.sysmail_help_status_sp;
-- State and length of the mail queue
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
-- Errors the external program logged
SELECT * FROM msdb.dbo.sysmail_event_log;

What each message status means

Check sysmail_allitems for the status of the messages[31]; the figure below places each status on the delivery path.

Status Meaning Where to look next
unsent The external program hasn't processed the message yet[31] Whether Service Broker started the external program, in sysmail_event_log[31]
retrying Database Mail tried to deliver to the SMTP server but couldn't, usually because of a network interruption, an SMTP server failure, or an incorrectly configured account[31] The account settings and the network path
sent The external program delivered the message to the SMTP server[31] The SMTP server's logs, because it accepted the message but didn't deliver it[31]
failed The external program was unable to deliver to the SMTP server[31] msdb.dbo.sysmail_event_log, which holds the detailed error[31]

If the SMTP server cannot be reached, check the actual Account Retry Attempts and Account Retry Delay settings with msdb.dbo.sysmail_help_configure_sp[32]. On Managed Instance, a missing outbound network security group rule for the SMTP port is one cause of that unreachability; The Agent email chain above gives the rule.

For a message stuck before sent, investigate Database Mail, account settings, network connectivity, and the SMTP server with the views above; for a sent message that never arrived, investigate delivery after SMTP acceptance.

Message queuedstatus: unsentExternal programsends to SMTPSMTP serveraccepts it?Noretrying,then sentor failedYesstatus: sentSMTP server's jobRecipient inboxoutside the mail log
Where each Database Mail status sits on the path from the mail queue to the recipient inbox.

Matching requirements to SQL Server Agent features

When several Agent objects sound right for a requirement or symptom, choose by what it is about, because SQL Server Agent uses its components to define the tasks to be performed, when to perform the tasks, and how to report the success or failure of the tasks[1]: each requirement is paired with the feature that meets it and why a related feature fails. The Microsoft DP-300 study guide[33] lists "Manage schedules for regular maintenance jobs", "Configure job alerts and notifications", and "Troubleshoot SQL Server Agent jobs" under Create and manage SQL Server Agent jobs (Configure and manage automation of tasks). Use each row with the qualifications in the named section above.

The scenario says Choose Why the tempting wrong answer fails
Run every 15 minutes between 01:00 and 05:00 Daily schedule with @freq_subday_type = 4, @freq_subday_interval = 15, and the active times (Encoding a recurrence) @freq_interval = 15 on a daily schedule means every 15 days
Run on the last weekday of each month @freq_type = 32, @freq_interval = 9, @freq_relative_interval = 16 (Encoding a recurrence) A fixed monthly day number does not follow the last weekday
Pause one of two jobs that share a schedule Disable that job, or detach the schedule from it (Schedules are shared objects) Disabling the shared schedule stops scheduled starts for both jobs
Email the team when a job fails, on Managed Instance A profile named AzureManagedInstance_dbmail_profile, an operator, When the job fails, and an outbound rule for the SMTP port (The Agent email chain) Selecting a profile in Agent Properties and restarting Agent is the SQL Server procedure; a profile with any other name is not used
Email on every failed job The job's Notifications page (Job notifications report a job's outcome) A severity alert matches logged errors, not job outcomes; a proxy supplies step credentials, not mail
Respond to severity-15 syntax errors A SQL Server event alert on severity 15, with those errors written to the application log (SQL Server event alerts) A performance condition alert compares counters, not error severities
Respond when free space in tempdb drops below 1,000 KB A performance condition alert, falls below 1000 (Performance condition alerts) An error-number alert needs a logged error that this condition does not produce
React to an event selected by a WQL query A WMI event alert (WMI event alerts) A performance condition alert only samples numeric counters
A CmdExec step can't read a file share, though the command works for you A proxy whose credential's Windows account can access the share (Who a job step runs as) A database role grants nothing on a Windows share
A T-SQL step fails with a permission error Check the step's database and the effective execution principal's permissions (Who a job step runs as) T-SQL steps don't use proxies, so assigning one changes nothing
An on-call administrator must start and stop other users' jobs SQLAgentOperatorRole (SQL Server Agent roles in msdb) SQLAgentReaderRole adds visibility; starting jobs stays limited to owned ones
A step failed but no failure email was sent Check whether the step flow quits with success after the failure (How step flow decides the job's outcome) Changing the operator doesn't change the job's outcome
A step fails transiently Retry attempts and a retry interval on the step (How step flow decides the job's outcome) A frequent extra schedule restarts the whole job instead of retrying the step
A query in a T-SQL step returns shortened values Set TEXTSIZE in the step (Capturing job step output) History retention controls how long records stay, not their length
The email shows as sent but never arrived The SMTP server's logs (Troubleshooting Database Mail delivery) Rerunning the job repeats the work without reaching the SMTP server's records
A failed T-SQL step on Managed Instance Job history: View History or sp_help_jobhistory (Completed job history; Current job activity) The Azure Monitor activity log records resource management operations, not job steps
Schedule a task in Azure SQL Database Elastic jobs, covered in Create and manage database tasks in Azure SQL Server Agent is not available in Azure SQL Database[7]

Before picking an answer, identify the platform (Azure SQL Database, Managed Instance, or SQL Server), then the object the requirement is about, and only then the setting.

Schedules, job notifications, and the three alert types: what fires each one and what it does

MechanismScheduleJob notificationSQL Server event alertPerformance condition alertWMI event alert
What makes it fireA one-time date and time, a recurring calendar rule, SQL Server Agent start, or CPU idleThe job finishes: when it succeeds, when it fails, or when it completesAn error number or severity level written to the Windows application logA counter instance falls below, becomes equal to, or rises above a valueA WMI event that a WQL query selects in a namespace on the Agent's computer
What it doesStarts the jobs it is attached toNotifies the selected operatorNotifies operators, runs a job, or bothNotifies operators, runs a job, or bothNotifies operators, runs a job, or both
Defined in T-SQL withsp_add_schedule, then sp_attach_schedulesp_update_job with @notify_level_emailsp_add_alert with @message_id or @severitysp_add_alert with @performance_conditionsp_add_alert with @wmi_namespace and @wmi_query
On Azure SQL Managed InstanceSupported, except the CPU idle scheduleEmail supported; pager and net send not supportedNot supportedNot supportedNot supported

Decision tree

Time, calendar rule,Agent start, CPU idle?YesScheduleNoReport the job'sown result?YesJobnotificationNoLogged error numberor severity?YesSQL Serverevent alertNoCounter comparedwith a threshold?YesPerformancecondition alertNoWMI event alertevent chosen by WQLManaged Instance: no alerts,no CPU idle schedule

Sharp facts the exam loves — give these one last read before exam day.

Cheat sheet

Sharp facts the exam loves — scan these before test day.

Agent schedules separate the eligible days from the interval within each day

SQL Server Agent uses @freq_type = 4 with @freq_interval to specify a daily recurrence, while @freq_subday_type and @freq_subday_interval determine repeated starts within each eligible day. Subday units are 2 for seconds, 4 for minutes, and 8 for hours, and the repeat interval must be at least 10 seconds. Set @active_start_time and @active_end_time in HHmmss format to constrain scheduled starts to the maintenance window; @active_start_date and @active_end_date use yyyyMMdd. Create the schedule with msdb.dbo.sp_add_schedule and attach it to the job with sp_attach_schedule.

Trap Using @freq_interval to express minutes between executions, although that parameter controls days when @freq_type is daily.

5 questions test this
Weekly and relative-monthly schedules encode different calendar rules

SQL Server Agent weekly schedules use @freq_type = 8 and combine weekday bit values in @freq_interval, with @freq_recurrence_factor specifying the number of weeks between runs. A fixed day of the month uses @freq_type = 16, whereas a rule such as the last weekday uses @freq_type = 32, @freq_interval = 9 for weekday, and @freq_relative_interval = 16 for last. Review the effective start date after attaching a weekly or monthly schedule because it determines the first eligible execution.

Trap Using a fixed monthly date for last-weekday maintenance, although the matching calendar date changes between months.

5 questions test this
A shared Agent schedule changes every job attached to it

SQL Server Agent schedules are reusable objects: several jobs can share one schedule, and a job can have several schedules. Updating a shared schedule with sp_update_schedule affects all attached jobs, so use a separate schedule when one job needs an independent maintenance window. Disabling a schedule suppresses that schedule for every attached job but still permits a job to start manually or through an alert; detach the schedule when only one association should be removed.

Trap Disabling a shared schedule to pause one maintenance job, which also suppresses scheduled starts for its other attached jobs.

5 questions test this
Agent activation conditions must fit the platform and the job's running state

SQL Server Agent supports one-time, recurring, and service-start schedules; @freq_type = 64 starts a job when Agent starts. Azure SQL Managed Instance keeps Agent running and activates service-start jobs after failover, but does not support the idle-CPU schedule available on SQL Server. Only one instance of a given Agent job can run at a time, so a manual start cannot create a second execution while its scheduled execution is still running.

Trap Using an idle-CPU trigger for Managed Instance maintenance, although that scheduling condition is unsupported on the managed service.

5 questions test this
Agent event alerts match logged SQL errors by number or severity

On SQL Server running on Windows, configure a SQL Server event alert for a particular error number or severity, such as severity 15 syntax errors, but the event must reach the Windows application log for Agent to detect it. Severities 19 through 25 are logged automatically; lower-severity events require sp_altermessage, RAISERROR WITH LOG, or xp_logevent to force logging. Database and message-text filters can narrow the match, but xp_logevent events occur in master, so the alert's @database_name must be 'master' or NULL for those events to trigger it. A matching alert can notify an operator or run a response job, making this the event-driven choice rather than a periodic maintenance schedule.

Trap Choosing a performance-condition alert for severity-15 errors, although a counter threshold does not match SQL error severity.

4 questions test this
Agent performance alerts require a counter and a threshold comparison

When a Windows SQL Server task should respond to a measured resource condition, use a SQL Server performance-condition alert and select its object, counter, and applicable instance. Specify whether the value rises above, falls below, or equals the threshold, and interpret the threshold in that counter's units. Performance counters are sampled periodically, so the alert can follow the threshold crossing after a short delay rather than at the exact instant it occurs.

Trap Choosing an error-number event alert for low tempdb free space, although the requirement concerns a sampled counter value rather than a specific logged error.

4 questions test this
WMI alerts subscribe to events rather than sampling performance counters

A SQL Server Agent WMI alert on Windows identifies an event source with a WMI namespace and uses a WQL query to select the event of interest. Choose it when the required trigger is exposed as a WMI event, rather than as a SQL error number or a numeric performance condition. Azure SQL Managed Instance does not support Agent alerts, so this WMI subscription is a SQL Server on Windows option.

Trap Using a performance-counter alert for a WQL-selected event, which monitors numeric samples instead of subscribing to the requested event source.

4 questions test this
Agent email needs a mail account, a profile, and an operator

SQL Server Agent email uses a Database Mail account for SMTP delivery, a profile containing the account, and an operator identifying the recipient. On SQL Server, enable the mail profile under Agent Properties, Alert System, select Database Mail and the appropriate profile, and restart Agent after configuring it. An operator is an alias for a person or group, while the profile defines how mail is sent; neither object by itself selects which job outcome sends a message.

Trap Creating an Agent proxy to enable failure email, although proxies supply job-step credentials rather than SMTP delivery configuration.

5 questions test this
Job notifications can report failure, success, or every completion without an event alert

When the requirement is to report an Agent job's final result, configure its Notifications page, choose the email operator, and select When the job fails, When the job succeeds, or When the job completes. The completion option includes both success and failure, whereas failure-only notification suppresses routine success messages. A separate SQL Server event alert is appropriate for a matching engine event, not a prerequisite for a job-result email.

Trap Creating a severity alert to report every failed job, although job failure is a job outcome and need not correspond to one selected engine severity.

5 questions test this
Managed Instance Agent email requires its designated Database Mail profile

Azure SQL Managed Instance Agent looks for a Database Mail profile named AzureManagedInstance_dbmail_profile and can use only that profile for its job notifications. Enable Database Mail if necessary, associate an SMTP account with that profile, and allow the required outbound SMTP connection through the instance's network security group. Managed Instance supports email job notifications but not pager notifications or NetSend; see the WMI alert-subscription rule for the separate alert-platform boundary. It does not require the SQL Server procedure of selecting a profile in Agent Properties and restarting Agent.

Trap Reusing a custom-named SQL Server mail profile unchanged on Managed Instance, whose Agent notification integration looks for the designated profile name.

5 questions test this
Agent database roles separate ownership, visibility, and operational control

SQL Server Agent's msdb roles form an increasing permission hierarchy: SQLAgentUserRole manages owned local jobs, SQLAgentReaderRole also sees other jobs and their history, and SQLAgentOperatorRole can start and stop all local jobs. Operator members can enable or disable other owners' local jobs and schedules through sp_update_job or sp_update_schedule using only the identifier and @enabled parameters. These roles do not let a non-sysadmin edit another owner's job definition or change its ownership, so monitoring access is not equivalent to unrestricted job administration. Alert creation is a separate permission boundary: by default, only sysadmin members can execute msdb.dbo.sp_add_alert, so membership in these job-management roles alone does not authorize creating alerts.

Trap Assigning SQLAgentReaderRole to an operator who must restart other users' jobs, although that role adds visibility rather than cross-owner execution control.

5 questions test this
T-SQL job steps use the job owner's context rather than an Agent proxy

A SQL Server Agent T-SQL step specifies the database in which its statements execute and does not use an Agent proxy. A non-sysadmin-owned job runs its T-SQL under the owner's context; a sysadmin-owned job uses the Agent service account context, and sysadmin can specify a database user with @database_user_name in sp_add_jobstep. When a statement works interactively but fails as a job, check the configured database and the effective principal's permissions rather than assuming the interactive session's identity carries over.

Trap Assigning a CmdExec proxy to repair a T-SQL permission failure, although the T-SQL subsystem does not execute through Agent proxies.

5 questions test this
Non-T-SQL job steps need credentials that can access their operating-system resources

On SQL Server, an Agent proxy supplies a credential-backed execution context for a permitted subsystem such as CmdExec or PowerShell. Grant the step identity the required share, file, and executable access, and use an explicit executable path when it is not available through that identity's PATH. A successful interactive test under an administrator's account does not establish that the scheduled identity can reach the same resources; Azure SQL Managed Instance does not support Agent proxies.

Trap Granting a database role to fix a CmdExec file-share access failure, although the failing authorization is for the Windows execution identity on the share.

5 questions test this
Agent activity and history answer different troubleshooting questions

Use SSMS Job Activity Monitor or msdb.dbo.sp_help_jobactivity to inspect the current execution state of Agent jobs. Use View History or msdb.dbo.sp_help_jobhistory to investigate completed attempts and step messages. Agent records activity in msdb.dbo.sysjobactivity for each service session, which can also help identify jobs that were running when the service terminated; a resource's Azure Activity log does not provide these database-engine job-step details.

Trap Reviewing the Managed Instance resource's Azure Activity log for a failed T-SQL job step, although that log records resource-management operations rather than Agent step history.

5 questions test this
Agent step routing determines which work runs next and how the job finishes

For each SQL Server Agent step, configure separate On success and On failure actions to continue, jump to a selected step, or quit with a success or failure result. A job can therefore report success after an earlier step fails if its configured failure path eventually quits successfully. For a CmdExec step, also verify the configured successful process exit code, because Agent uses that code to interpret the external program's result before applying the routing policy.

Trap Changing the failure-email operator when a failed maintenance step is followed by a successful job result, although the job's control flow determines the outcome being notified.

5 questions test this
Agent retries are a step policy rather than a recurring-job schedule

Configure an Agent step's Retry attempts and Retry interval in minutes when a transient failure should be retried before the step is treated as failed. The Advanced page supports 0 through 9999 retry attempts and, when retries are enabled, an interval of 1 through 9999 minutes. After retries are exhausted, the step follows its configured failure action, so coordinate the retry budget with the route that should handle an unrecovered failure.

Trap Adding a frequent recurring schedule to recover a transient step failure, which restarts the whole job on future schedule events rather than applying a bounded step retry policy.

5 questions test this
Agent step output needs deliberate capture when job history is insufficient

SQL Server Agent can capture supported step output to an operating-system file or msdb.dbo.sysjobstepslogs, with overwrite or append behavior selected explicitly. File output requires a sysadmin-executed step, whereas members of the Agent msdb roles can use table output; output included in history can be truncated. T-SQL steps also change TEXTSIZE, so explicitly set TEXTSIZE in the step when returned large character or binary column values are unexpectedly shortened even though the query works in SSMS.

Trap Increasing job-history retention to recover truncated query values, although retention controls how long history remains rather than the step's TEXTSIZE setting or output destination.

5 questions test this
Database Mail distinguishes queue processing from SMTP acceptance and final delivery

A sent item means the SMTP server accepted the message, not that it reached the recipient's inbox, so investigate downstream mail delivery when sysmail_sentitems shows success. For messages not accepted by SMTP, inspect Database Mail's status and queue with msdb.dbo.sysmail_help_status_sp and sysmail_help_queue_sp, then review sysmail_event_log for sending errors. Database Mail requires Database Mail XPs to be enabled and Service Broker to be enabled in msdb.

Trap Rerunning a successful maintenance job to diagnose an email already accepted by SMTP, which repeats the database work without investigating downstream mail delivery.

5 questions test this

References

  1. https://learn.microsoft.com/en-us/ssms/agent/sql-server-agent
  2. https://learn.microsoft.com/en-us/ssms/agent/manage-job-steps
  3. https://learn.microsoft.com/en-us/ssms/agent/create-and-attach-schedules-to-jobs
  4. https://learn.microsoft.com/en-us/ssms/agent/operators
  5. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql
  6. https://learn.microsoft.com/en-us/ssms/agent/sql-server-agent-fixed-database-roles
  7. https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview
  8. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-on-azure-vm-iaas-what-is-overview
  9. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance
  10. https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail
  11. https://learn.microsoft.com/en-us/ssms/agent/alerts
  12. https://learn.microsoft.com/en-us/ssms/agent/notify-an-operator-of-job-status
  13. https://learn.microsoft.com/en-us/ssms/agent/create-a-sql-server-agent-proxy
  14. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
  15. https://learn.microsoft.com/en-us/ssms/agent/monitor-job-activity
  16. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql
  17. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql
  18. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-schedule-transact-sql
  19. https://learn.microsoft.com/en-us/training/modules/schedule-tasks-using-sql-server-agent/3-describe-task-status-notifications
  20. https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-overview
  21. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-alert-transact-sql
  22. https://learn.microsoft.com/en-us/ssms/agent/create-an-alert-using-severity-level
  23. https://learn.microsoft.com/en-us/ssms/agent/create-a-wmi-event-alert
  24. https://learn.microsoft.com/en-us/ssms/agent/implement-sql-server-agent-security
  25. https://learn.microsoft.com/en-us/ssms/agent/set-job-step-success-or-failure-flow
  26. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-jobactivity-transact-sql
  27. https://learn.microsoft.com/en-us/azure/azure-monitor/platform/activity-log
  28. https://learn.microsoft.com/en-us/ssms/agent/view-the-job-history
  29. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-jobhistory-transact-sql
  30. https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-general-troubleshooting
  31. https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-mail-queued-not-delivered
  32. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-configure-sp-transact-sql
  33. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300