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].
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.
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
- 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]. - Database Mail profile: a named set of accounts. The sample creates the profile with
sysmail_add_profile_spand adds the account withsysmail_add_profileaccount_sp[9]. - 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]. - 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.
Managed Instance Agent email, end to end
This listing follows the sample on Automate management tasks using SQL Agent jobs in Azure SQL Managed Instance[9]. Replace the $(...) placeholders and the sample names with your own values, and add the outbound network security group rule for your SMTP port before testing.
Database Mail and job-failure email on Azure SQL Managed Instance
-- 1. Enable Database Mail if it isn't already enabled
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
GO
-- 2. The account holds the SMTP server details
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL Agent Account',
@description = 'Mail account for Azure SQL Managed Instance SQL Agent system.',
@email_address = '$(loginEmail)',
@display_name = 'SQL Agent Account',
@mailserver_name = '$(mailserver)',
@username = '$(loginEmail)',
@password = '$(password)';
-- 3. The profile name is fixed: Agent on Managed Instance uses only this one
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'AzureManagedInstance_dbmail_profile',
@description = 'E-mail profile used for messages sent by Managed Instance SQL Agent.';
-- 4. Put the account into the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'AzureManagedInstance_dbmail_profile',
@account_name = 'SQL Agent Account',
@sequence_number = 1;
-- 5. Send a test message through the profile
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = 'ADD YOUR EMAIL HERE',
@body = 'Add some text',
@subject = 'Azure SQL Instance - test email';
-- 6. The operator is the recipient alias (a distribution group is ideal)
EXEC msdb.dbo.sp_add_operator
@name = N'AzureSQLTeam',
@enabled = 1,
@email_address = N'AzureSQLTeam@contoso.com';
-- 7. The job notification decides when mail is sent: here, on failure
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';
Step 1 is required because Database Mail isn't automatically enabled[30]. You can confirm the email's success or failure in the Database Mail log in SSMS[9].
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.
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]:
- Object: the area of performance to be monitored[11].
- Counter: an attribute of that area[11].
- Instance: the specific instance, if any, of the attribute to be monitored[11].
- Alert if counter and Value: the behavior is falls below, becomes equal to, or rises above, and the value is a number that describes the counter[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:
- Namespace: SQL Server Agent registers as a WMI client to the WMI namespace you provide[11]. Only WMI namespaces on the computer that runs SQL Server Agent are supported[23].
- Query: a WMI Query Language (WQL) statement that identifies the specific event[11].
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:
- You must create the credential before the proxy, and the credential's user must have the Access this computer from the network permission on the SQL Server computer[13].
- SQL Server Agent checks the proxy's subsystem access each time the step runs; if the proxy no longer has access, the step fails[13].
- Creating a proxy doesn't change the permissions of the credential's user[13], so the account still needs its own rights to every resource the step uses.
- Only members of sysadmin can create, modify, or delete proxies; other users need one of the msdb Agent roles to use them[13], and the creator of a job step must have access to the proxy[5].
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.
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.
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.
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.
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
| Mechanism | Schedule | Job notification | SQL Server event alert | Performance condition alert | WMI event alert |
|---|---|---|---|---|---|
| What makes it fire | A one-time date and time, a recurring calendar rule, SQL Server Agent start, or CPU idle | The job finishes: when it succeeds, when it fails, or when it completes | An error number or severity level written to the Windows application log | A counter instance falls below, becomes equal to, or rises above a value | A WMI event that a WQL query selects in a namespace on the Agent's computer |
| What it does | Starts the jobs it is attached to | Notifies the selected operator | Notifies operators, runs a job, or both | Notifies operators, runs a job, or both | Notifies operators, runs a job, or both |
| Defined in T-SQL with | sp_add_schedule, then sp_attach_schedule | sp_update_job with @notify_level_email | sp_add_alert with @message_id or @severity | sp_add_alert with @performance_condition | sp_add_alert with @wmi_namespace and @wmi_query |
| On Azure SQL Managed Instance | Supported, except the CPU idle schedule | Email supported; pager and net send not supported | Not supported | Not supported | Not supported |
Decision tree
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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job uses a daily schedule that repeats every 10 minutes. Scheduled starts must occur only between 01:00 and 05
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You created a schedule named Sched1 by running msdb.dbo.sp_add_schedule. Sched1 is enabled. A job named Job1 has never started on
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are creating a daily schedule for a SQL Server Agent job by using sp_add_schedule with @freq_type = 4 and @freq_interval = 1.
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent maintenance job must run once at 22:00 every other day. You are creating the schedule by using sp_add_schedule with @freq_subday
- You have an Azure SQL Managed Instance named SQLMI1. You run sp_add_schedule with @freq_type = 4, @freq_subday_type = 2 and @freq_subday_interval = 5 to start a polling job every five seconds. The sch
- 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
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent job must run on Sunday every second week. You are creating the schedule by using sp_add_schedule with @freq_type = 8 and @freq_i
- You have an Azure SQL Managed Instance named SQLMI1. On Wednesday, November 11, 2026, you run sp_add_schedule with @freq_type = 8, @freq_interval = 2, @freq_recurrence_factor = 2 and @active_start_dat
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You review a schedule that a former administrator created for a statistics job by running sp_add_schedule with @freq_type = 32, @
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job must run every Monday, Wednesday and Friday at 23:00. You are creating the schedule by using sp_add_schedu
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. At 10:00 on Monday, November 9, 2026, you attach a schedule that runs every other Monday at 08:00 to a new job named Job1. Job1 m
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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A schedule named Sched1 is attached to four jobs. One of the jobs is currently running. You run sp_update_schedule to change the
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Five SQL Server Agent jobs share a schedule named Nightly that starts at 01:00. One job named Job3 must start at 03:00 instead. T
- You have an Azure SQL Managed Instance named SQLMI1. A schedule named Sched1 is attached to Job1 and Job2. Job1 must permanently stop using Sched1 and will later get a different schedule. Job2 keeps r
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A schedule named Sched1 was disabled during an outage. Afterward, you edited Sched1 to change its start time. The jobs attached t
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You disabled a schedule named Sched1, which is attached to a job named Job1. Job1 has no other schedules. The job history shows t
- 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
- You have an Azure SQL Managed Instance named SQLMI1. A data correction job named Job1 must run exactly once, at 23:00 on December 5, 2026, after a vendor release. Job1 must not run again unless an adm
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A job named Job1 runs a load procedure for the EU region every night and takes about three hours. A US load that uses the same pr
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A cache-warming job named Job1 must run each time the SQL Server Agent service starts and also every day at 06:00. The solution m
- You have an Azure SQL Managed Instance named SQLMI1. A job named Job1 uses a schedule of type Start automatically when SQL Server Agent starts. Job1 ran at 03:12 today, although nobody started it and
- You have an Azure SQL Managed Instance named SQLMI1. A job named Job1 uses a Start automatically when SQL Server Agent starts schedule and is idle. You need to run Job1 now to test it. The solution mu
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A stored procedure in a database named Sales uses xp_logevent to log message 60000. An alert for message 60000 with @database_nam
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An application raises error 50010 WITH LOG when an import batch is rejected. You need a cleanup job named Job1 to run as soon as
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An application raises user-defined message 50001 at severity 16 by using RAISERROR without WITH LOG. An error number alert for 50
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts a production database named Prod and a reporting database named Rpt. You need an alert for severity 17 errors that occu
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts databases named db1 and db2. You are creating a performance condition alert that must fire when the transaction log of
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 with a performance condition alert on a lock wait counter. During a test, the counter crosses the threshold, but the alert respons
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are creating a performance condition alert on the SQLServer:Transactions object. The alert must fire when the free space in t
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. During memory pressure, pages leave the buffer pool too quickly. You need a SQL Server Agent alert that notifies Operator1 when p
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You need an Agent job to run whenever a table in the Sales database is altered. Altering a table raises no error, and the event i
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that uses a WMI event alert to start a job when a DDL event occurs. You plan to migrate the databases and Agent jobs to an Azure S
- You have SQL Server on Azure Virtual Machines instances named SQLVM1 and SQLVM2. On SQLVM1, you create a WMI event alert whose namespace points to the server events namespace on SQLVM2. The alert neve
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are creating a SQL Server Agent WMI event alert in SSMS. The alert must respond to ALTER_TABLE events in a database named Sal
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. SQL Server Agent on SQLVM1 already sends email through Database Mail. You need to define a single alias for the database team so
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. SQL Server Agent sends notifications by using a Database Mail profile named Profile1 that contains one account for an SMTP server
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Database Mail on SQLVM1 has an account and a profile, and test messages sent by using sp_send_dbmail arrive. Job1 is configured t
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. SQL Server Agent sends job notifications by using Database Mail. The SMTP relay that the Database Mail account uses requires a us
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. In SQL Server Agent Properties, you select Enable mail profile on the Alert System page, choose Database Mail and select a profil
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. SQL Server Agent sends email through Database Mail to an operator named Operator1. You need Operator1 to be emailed whenever a jo
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Export1 creates a nightly file. An operator named Team1 starts a downstream process from the file. T
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are scripting job notifications by using sp_update_job. A job named Job1 must send email to an existing operator named DBA1 w
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 already names an email operator. You need to use sp_update_job so that the operator receives an
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 emails an operator named Operator1 after every run. Operator1 now wants email only when Job1 fa
- 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
- You have an Azure SQL Managed Instance named SQLMI1. A Database Mail account named Account1 and a profile named AzureManagedInstance_dbmail_profile both exist. Agent job notifications are not sent bec
- You have an Azure SQL Managed Instance named SQLMI1. You plan to configure email notifications for SQL Server Agent jobs. When you try to create a Database Mail account, the Database Mail procedures a
- You have an Azure SQL Managed Instance named SQLMI1. SQL Server Agent notifications use AzureManagedInstance_dbmail_profile, which sends through an authenticated SMTP relay on port 587. The messages f
- You have an Azure SQL Managed Instance named SQLMI1 with a working AzureManagedInstance_dbmail_profile. An operator named Operator1 has an email address and a pager address. Job1 is configured to page
- You have an Azure SQL Managed Instance named SQLMI1. You migrated Database Mail from SQL Server, including a profile named DBA_Mail that contains a working SMTP account. SQL Server Agent job email not
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentOperatorRole. A job named Job2 is owned by User2. User1 must disable Job2 so that it does not run du
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A developer who owns 12 SQL Server Agent jobs is leaving, and the jobs must be reassigned to a login named svcJobs. User1, a memb
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A sysadmin grants SQLAgentUserRole access to a CmdExec proxy named Proxy1. User2 is a member of SQLAgentOperatorRole only, and Us
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentUserRole in msdb and owns three SQL Server Agent jobs. User1 must be able to delete the job history
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentOperatorRole in msdb. User1 runs msdb.dbo.sp_add_alert to create a severity alert, and the statement
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts a database named db1. A script adds a Transact-SQL step to Job1 by using sp_add_jobstep without specifying a database.
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A job named Job1 is owned by User1, who isn't a sysadmin. A script set @database_name = N'db1' and @database_user_name = N'Report
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A job named Job1 is owned by a sysadmin login and has a Transact-SQL step in db1. The step does not specify a database user. An a
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Job1, owned by a non-sysadmin login named User1, has a Transact-SQL step that fails with a permission error on Table2 in db1. An
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A job named Job1 is owned by a sysadmin login. A Transact-SQL step in Job1 must run in db1 under the permissions of a database us
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Job1 has a PowerShell step that runs as a proxy named Proxy1. After a security review, the step fails because Proxy1 no longer ha
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Job1 has an Operating system (CmdExec) step that runs as a proxy named Proxy1 and copies files to a share named \\FS1\Exports. Th
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A CmdExec step in Job1 runs a program named extract.exe from D:\Tools by using a proxy. The step fails because the program cannot
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentUserRole and is not a sysadmin. User1 must create a job step that runs an operating system command.
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A sysadmin-owned job has a CmdExec step that copies files to \\FS1\Exports and runs without a proxy. The step fails with access d
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs 25 SQL Server Agent jobs. You need to use Transact-SQL to list every failed job and failed job step recorded in the job
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs 40 SQL Server Agent jobs. Users report slow queries during business hours. You need to identify which Agent jobs on SQLV
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 has three steps. You need to use Transact-SQL to return the runtime state of Job1, including th
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Overnight, the SQL Server Agent service on SQLVM1 stopped unexpectedly and was restarted 30 minutes later. You need to determine
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. The copy step of an Agent job named NightlyCopy retries up to three times when the network drops. A weekly report must show how m
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 has three steps. Step 2 failed last night, but Job1 reported success and no failure email was s
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent job named Job1 has five steps. Step 5 removes staging tables. You need to ensure that if step 1 fails, Job1 skips steps 2 throug
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A deployment script creates Job1 and adds three Transact-SQL steps by calling sp_add_jobstep with only @job_name, @step_name, @su
- You have an Azure SQL Managed Instance named SQLMI1. Job1 has three steps. Step 3, the last step, refreshes an optional reporting summary table and must still run after step 2 succeeds. When step 3 fa
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job step runs a vendor executable as an Operating system (CmdExec) step. The executable returns exit code 1 wh
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A script created a SQL Server Agent job step by using sp_add_jobstep with only the job name, step name, subsystem and command. Th
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent job step is configured with three retry attempts and an On failure action of Go to step 5. The step fails on every attempt. When
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent job named Job1 has four steps. Only step 3 calls a remote service that occasionally times out. You need to retry the remote call
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 copies data from a linked server. The copy step occasionally fails because of a brief network o
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are adding a step to a SQL Server Agent job named Job1 by using sp_add_jobstep. If the step fails, it must be retried up to f
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentUserRole and owns a SQL Server Agent job named Job1. User1 needs to capture the full output of the T
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A sysadmin owns a SQL Server Agent job with a Transact-SQL step that returns several result sets. The step is configured to inclu
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent Transact-SQL job step returns nvarchar(max) values. The query returns complete values in SSMS, but the values
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Several SQL Server Agent job steps on SQLVM1 are configured to log their output to a table. You need to query the logged output a
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are adding a Transact-SQL step to a SQL Server Agent job by using sp_add_jobstep. The step must log its output to a table in
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Agent notifications remain queued, sysmail_event_log has no entries, and sysmail_allitems shows no status updates. The is_broker_
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Agent notifications in sysmail_allitems have shown the retrying status for several hours, and none of them has reached the SMTP s
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A maintenance job emails an operator when it completes. msdb.dbo.sysmail_sentitems lists last night's notification, but the opera
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Several Agent job notifications appear in msdb.dbo.sysmail_faileditems. You need to retrieve the error message that the Database
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Job failure notifications have stopped arriving, and Service Broker is enabled in msdb. You need to determine whether Database Ma
References
- https://learn.microsoft.com/en-us/ssms/agent/sql-server-agent
- https://learn.microsoft.com/en-us/ssms/agent/manage-job-steps
- https://learn.microsoft.com/en-us/ssms/agent/create-and-attach-schedules-to-jobs
- https://learn.microsoft.com/en-us/ssms/agent/operators
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql
- https://learn.microsoft.com/en-us/ssms/agent/sql-server-agent-fixed-database-roles
- https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-on-azure-vm-iaas-what-is-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance
- https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail
- https://learn.microsoft.com/en-us/ssms/agent/alerts
- https://learn.microsoft.com/en-us/ssms/agent/notify-an-operator-of-job-status
- https://learn.microsoft.com/en-us/ssms/agent/create-a-sql-server-agent-proxy
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
- https://learn.microsoft.com/en-us/ssms/agent/monitor-job-activity
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-schedule-transact-sql
- https://learn.microsoft.com/en-us/training/modules/schedule-tasks-using-sql-server-agent/3-describe-task-status-notifications
- https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-alert-transact-sql
- https://learn.microsoft.com/en-us/ssms/agent/create-an-alert-using-severity-level
- https://learn.microsoft.com/en-us/ssms/agent/create-a-wmi-event-alert
- https://learn.microsoft.com/en-us/ssms/agent/implement-sql-server-agent-security
- https://learn.microsoft.com/en-us/ssms/agent/set-job-step-success-or-failure-flow
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-jobactivity-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-monitor/platform/activity-log
- https://learn.microsoft.com/en-us/ssms/agent/view-the-job-history
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-jobhistory-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-general-troubleshooting
- https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-mail-queued-not-delivered
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-configure-sp-transact-sql
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300