SQLTreeo Job Timeline · How-to

Jobs in availability groups

How availability group jobs are shown and compared: instance jobs per replica with its role, the Replicas view, contained groups and failovers.

SQLTreeo Job Timeline shows the SQL Server Agent jobs of your availability groups, and says for a job on which replica it does its work.

It shows both kinds. Most jobs belong to an instance: every replica has its own msdb with its own copy of a job. Contained availability groups, new in SQL Server 2022, have their own msdb, and the jobs in it move with the group.

Instance jobs: one row per replica

Add every replica by its own name. The Jobs view then lists a job once per replica, for example Nightly ETL on SQL01 and Nightly ETL on SQL02, each with its own schedule, enabled state and history.

The AG column says which availability group a job works for, and which role this server has in that group:

  • SalesAG (primary here)
  • SalesAG (secondary here)

That explains a copy that is disabled on the secondary, and it makes a copy that is enabled there stand out. A job is linked to a group when one of its steps runs in a database of that group. The server list shows the roles per server as well, for example SalesAG primary · HRAG secondary.

Compare the replicas

When a ticked server reports an availability group, a Replicas view appears after Jobs. It compares the copies of each job on the replicas of a group and says what would be missing or switched off after a failover. A badge on it counts the findings and group notes that are a risk or, when there are none, those that need attention. Distributed groups and a connection through the listener of a contained group do not make the view appear.

Instance jobs are matched by name within a group. A job of which some copy works in another group only is left to that group. The jobs inside a contained group are compared with the instance jobs of the same name only.

  • The replicas. One row per replica: its role now, how a failover to it works (Automatic, Manual, synchronous, Forced, asynchronous, Never), whether it is part of the comparison, and its jobs, enabled jobs, jobs missing and jobs to enable after a failover. A replica of the group that is not in the workspace is listed and named, and never counted as missing a job. Add replicas… opens Add many servers on its Availability groups source.
  • The findings. One row per job and finding. What limits the comparison of a group comes first, then the findings, most severe first: red is a risk, amber needs attention, grey is a note.
Finding When
Missing on the failover partner, or on a replica The job is enabled on the primary and a replica that can take over has no job of that name.
To enable after a failover The job is enabled on the primary and the copy elsewhere is switched off, or only its schedule is.
Enabled on a secondary The job is enabled with a schedule on a secondary, works in the databases of the group and does not check whether it runs on the primary. Red; amber when its last run there succeeded, because it may only read.
Disabled on the primary, enabled elsewhere The switch after a failover was probably not made.
Disabled on every replica The job is switched off on the primary and no enabled copy is in the comparison.
Not on the primary A copy exists on another replica only.
Schedules differ, steps differ The copies are not the same job: other schedule definitions, another number of steps, or per step another subsystem, database, kind of work or primary check.
In the group and on the instance A job in a contained group has an instance job of the same name; red when both are enabled.
  • One job on every replica. Select a finding. The replica grid then shows that job on each replica (enabled or not, schedule, steps, last run), and a strip at the bottom says in full what the finding means. History shows every copy on the history timeline. Copy and Copy list put the finding or the list on the clipboard, for a failover runbook or a ticket.
  • Filters. Only jobs in the group’s databases leaves out jobs of which no step runs in a database of the group, Hide notes shows only red and amber, and the job filter above the views narrows the findings as well.
  • How a replica takes over comes from the definition of the group: synchronous commit with automatic failover on both sides makes the automatic failover partner, synchronous commit with manual failover a planned failover, asynchronous commit a forced one. A configuration-only replica never takes over.
  • Nothing is guessed. Only what a server says now counts. The primary is the server that says so itself; when none does, the name that the other replicas report counts when they agree and the named server does not say something else. A server whose last read failed settles nothing. A replica is a secondary when it says so, or, when it could not tell, by elimination against a known primary. Without a known primary the findings that need to know the primary are not made. What remains is neutral (not on every replica, enabled on some replicas only, other schedules or steps), plus the findings that need only the role of one replica or the job itself, such as a job enabled on a secondary without a primary check. When a server cannot list the replicas of its groups, it gets a note and the view says that the list is unknown where it can still show the group; a group that no server can list may be a distributed group and is then left out.

“Skips its work on this replica”

Many maintenance jobs are enabled on every replica and decide at run time whether this replica should do the work. SQLTreeo Job Timeline recognizes such a job by its step commands: a call to sys.fn_hadr_is_primary_replica or sys.fn_hadr_backup_is_preferred_replica, or the keywords AVAILABILITY_GROUP_DATABASES, USER_DATABASES and @AvailabilityGroups as used with Ola Hallengren’s maintenance solution. The check runs on the server; the command text is not transferred. A job of this kind without a step in a group database is linked to every group on the instance.

On the replica where it is not its turn, such a job starts on schedule and exits within seconds. The AG column then adds (skips its work on this replica). This is how the application decides:

  1. A step that calls sys.fn_hadr_is_primary_replica works on the primary only. When this server is secondary for every group the job is linked to, the job skips its work here. When it is primary for every one, it works here.
  2. Otherwise the job’s own successful runs decide. A job whose latest run was real work, works here. A job whose latest runs took seconds, after at least three real runs in a row, skips its work here: that is the shape a failover leaves.
  3. When neither can tell, the job counts as working here.

What follows from it:

  • Long-running. The seconds-long exits are left out of the job’s baseline when enough real runs remain. The baseline then carries the note baseline from active-replica runs, and the first real run after a failover is not reported as far too long.
  • Rhythm. The scheduled runs of a job that skips its work here are drawn hollow. They take no part in the overlap analysis and are not reported as missed.

A step that uses sys.fn_hadr_is_primary_replica to do its work on the secondary is judged the wrong way round.

Contained availability groups

The jobs inside a contained availability group are stored in the group’s own msdb. From a replica that database is visible as <group name>_msdb.

  • Connect to a replica by its own name. The application reads the instance’s jobs and, when the group’s msdb is readable there, the jobs of the group. The group’s jobs carry the group in the Server column: AG SalesAG (via SQL01).
  • The group’s jobs are shown once. However many replicas of the group are in the workspace, the jobs of the group are read from one of them: the primary, when it is in the workspace and its last read worked.
  • A replica that cannot read the group’s msdb shows a note, for example on a secondary that allows no connections: Contained availability group SalesAG: its msdb (SalesAG_msdb) is not readable on this replica (secondary), so its jobs are not shown from this server. With the primary in the workspace as well, nothing is missing.
  • A connection through the listener of a contained group is inside the group. There, msdb is the group’s msdb. Such a connection shows the group’s jobs only, not the jobs of the instance behind it. The server list says so: Inside contained AG SalesAG (primary now on SQL01): shows the group’s jobs only. The listener and the replicas can be in the workspace together; the group’s jobs are still shown once.
  • Jobs of a contained group always count as doing their work. The group’s primary runs them, whichever replica the msdb is read from.

The listener of a classic group

The listener of a group that is not contained leads to the instance that is primary now, and shows that instance’s jobs. When that replica is already in the workspace, the listener is refused as a second name for the same instance.

Add the replicas by their own names instead. A listener shows the jobs of one replica only, and after a failover the same name shows the jobs of another instance. The application then puts a dated note on the server: Since 2026-10-03 14:05 this name connects to SQL02 (was SQL01): it points at another replica, probably after a failover. The jobs shown are those of SQL02.

The connection never asks for read-only routing, so a listener does not send the application to a readable secondary.

After a failover

The light read, every 30 seconds by default, reads the role of this replica in each group. When a role changed, a group appeared or went, or the msdb of a contained group became readable or unreadable, the server is read in full at once. The jobs of a contained group are then taken from the new primary, without waiting for the next full read.

A connection through the listener of a contained group notes the failover: The availability group failed over at about 2026-10-03 14:05: its primary is now SQL02 (was SQL01).

Notes appear on the server in the server list, in the Notes column of the Servers view, and in the bar Not everything could be read above the views.

Permissions

Permission What it gives Without it
VIEW SERVER STATE The role of this replica in each group, and which replica is the primary now The role shows as unknown, with the note The replica role of availability groups is unknown: VIEW SERVER STATE is needed to read it. The AG column then names the group without the role. The Replicas view still lists the replicas, but when nothing tells which one is the primary it makes only the comparisons that need no role
VIEW ANY DEFINITION The groups themselves: names, databases, listeners, and the list of their replicas The groups are hidden from the login. The note says that the jobs of contained availability groups are not read and that replica roles are unknown
Read access to <group name>_msdb The jobs inside a contained group, read from a replica The note that the group’s msdb is not readable on this replica

On SQL Server 2022 and later, the groups are visible to a login with VIEW ANY DEFINITION together with VIEW SERVER STATE or VIEW SERVER PERFORMANCE STATE.

Inside a contained group, through its listener, a login without VIEW ANY DEFINITION in the group cannot see the group’s name. The jobs are read, and the group is shown as group- followed by the first characters of its id.

The script for a login with these permissions is in Permissions and what is read.

Add the other replicas

With one replica in the workspace, the application can find the others:

  1. Open Add many servers and choose Availability groups.
  2. Press Find replicas and listeners. Every server in the workspace that has Always On enabled is asked for the replicas and listeners of its groups; one that needs a password, has an error or refused the login is skipped and reported.
  3. The replicas are ticked. Listeners are listed but not ticked. Distributed availability groups are left out: their replicas are availability groups, not servers.
  4. Choose how to sign in and press Add.

See Add many servers at once.

Limits

  • The Replicas view compares what the tool can see. Jobs that a product creates per server under its own names, such as replication agents and Reporting Services subscriptions, show as missing on the other replicas: the job filter and Only jobs in the group’s databases are the way around them. A check of the replica role that the tool does not recognise reads as no check. Two copies with the same steps in shape can still run different commands, because the tool never reads the commands of job steps. Distributed availability groups are not compared. To compare the jobs, operators and logins of two servers in full, and script what is missing, see Compare SQL Agent jobs and logins between two servers with SQLTreeo Compare.
  • Which group a job targets is derived, not read. It comes from the databases of the job’s steps. A job that decides at run time where it works, and whose steps name no group database, is linked to every group on the instance.
  • A login that cannot see the groups gets a note, not the jobs. From a replica, the jobs of a contained group are not read for such a login.
  • The label of a contained group does not show the role of its reader. AG SalesAG (via SQL01) does not say whether SQL01 is the primary.
  • Baselines are not kept per role. After a failover a job keeps one baseline per replica, with the seconds-long exits left out as described above.

So far the availability group handling has run against one real estate: a contained availability group with three replicas on SQL Server 2022, next to a failover cluster instance and stand-alone instances. All jobs there were instance jobs, defined on each replica. Jobs stored inside a contained group and a connection through the listener of a contained group are covered by automated tests with a simulated group, not yet by a real estate. A failover while the application is open has not been observed on a real estate either. The Replicas view has not met a real availability group yet: it is covered by automated tests with made-up replicas and by a simulated group. If the application shows something else than this article describes, send the diagnostics report: it holds the group ids, the roles, which msdb each connection read and, for the Replicas view, what the comparison made of every group.