Agent Activity

Overview

Three questions that are always asked together and are answered in three different places:

  • Is Agent up, and since when? dbo.syssessions.
  • What is running right now? dbo.sysjobactivity.
  • What happened? dbo.sysjobhistory.

None of the three is any use without the other two. A job that shows as running in sysjobactivity may have been running since a service restart three weeks ago, and the only way to know is the session it belongs to.

The Agent Activity page: sessions, what is running now, and what happened
The whole page in the 24 hours window. The header answers all three questions at once, the lanes put them on one axis, and the grid lists the runs newest first.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Agent Activity.

The node is shown only on an instance that can have SQL Server Agent. Express has the full set of Agent tables in msdb and no service to write to them, so every Agent page there would draw a correct and useless empty page.

The two traps this page exists to avoid

Both of them produce a confident wrong answer, which is worse than no answer at all.

sysjobactivity keeps the rows from every earlier Agent session

A job that was running when the service stopped has a start with no stop, forever. A page that does not filter on the current session reports it as running now, years later.

Every row on this page is filtered on the newest session id.

A job asked to start is neither running nor idle

A job with a run_requested_date and no start_execution_date has been asked to start and has not begun. Calling it running is wrong; calling it idle is wrong too, and it is what makes a second start attempt fail with:

the job already has a pending request

The page shows that state under its own name.

Reading the chart

The lanes: one per job, with Agent's own session in the top lane
The two lanes drawn as one long bar are runs that started when Agent did and are still going, which is exactly the state this page exists to report correctly. The red marks are the failures the header line counts.

One lane per thing, one mark per event, on a shared time axis.

  • Agent’s own lane is at the top, showing its sessions. A restart is a hole in the top line rather than a row in a list.
  • Every job lane below it is on the same axis, so the run that was killed by the restart lines up with the hole.
  • Marks come in two shapes: a point for something that happened at a moment, and a bar for something that lasted, drawn from its start to its end. A span shorter than three pixels is still drawn three pixels wide, because a real one second run that vanishes is worse than a slightly overstated one.
  • The axis is labeled at both ends and in the middle rather than at every tick, and the label format is chosen from the window.

Click a lane mark to select the matching grid row.

The toolbar

Control Options Default
Window 24 hours, 7 days, 30 days 24 hours
Job history Opens the Job History report.
Failed jobs Opens the Failed Jobs report.
Who owns them Opens Agent Security.

There is no auto refresh, deliberately. Reading job history in a loop means reading the table Agent takes an exclusive lock on at the end of every job, and a monitoring page is not worth queueing somebody’s overnight run behind. Use the toolbar refresh when you want a new read.

Reading the grid

The grid: state, when it started, how long it took and what it said
State first. The rows reading Running now are the long bars in the lanes above, and What it said is the message from the history row.
Column What it is
Job The job.
State Running, waiting for a worker, a pending start request, succeeded, failed, canceled or retried.
Started When this run started, or when the start was requested for a job that has not begun.
Took Elapsed. For a run still going, how long it has been going.
Step Which step it is on, or which step it stopped at.
What it said The message from the history row.

Where the data comes from

Source What it gives
dbo.syssessions Agent’s sessions, which is its uptime and its restarts.
dbo.sysjobactivity What is running now, filtered to the newest session.
dbo.sysjobhistory What happened, inside the window.
dbo.sysjobs, dbo.sysjobsteps The job and step names.

The Agent time encoding. run_date, run_time and run_duration are integers holding yyyymmdd, hhmmss and hhmmss. Every one of them looks like a number that could be compared or subtracted, and none of them can: run_duration of 130 is one minute thirty seconds, not one hundred and thirty of anything. The conversion is done once, in one place, for every page in this suite.

Messages you may see

SQL Server Agent has never run on this instance. syssessions is empty, which means the Agent service has not started since msdb was built. Nothing scheduled here has ever run.

No job has run in the last 24 hours. Agent has been up since (the session start) and nothing has run inside this window. Widen it from the toolbar.

Report Why you would go there
Job History Every run of every job, rather than the current session.
Failed Jobs The failures on their own timeline.
Job Step Failures Which step failed, and what it actually said.
Missed Runs What was supposed to run and did not, which is the failure this page cannot see.
Agent Security Who owns these jobs, and who can start them.
Subsystem Load Why a job that shows as running has been waiting rather than working.

Frequently asked questions

A job shows as running and I know it is not. If it is on this page it is in the current Agent session, so it really is running as far as Agent is concerned. A job stuck in that state usually has a step waiting on a lock or on a subsystem worker.

What is the difference between this and Job History? Job History is a record. This page is the current state of the service, with history drawn beside it for context. The Agent lane at the top exists on this page and nowhere else.

Why does the window not go back further than 30 days? Because Agent trims sysjobhistory to a row count, not to an age. On a busy instance the default limit holds about twenty minutes. Agent Settings is the page that says what yours is.