Job history, baselines and long runs

Two questions need more history than SQL Server Agent keeps: what ran last week, and is this run longer than normal for this job. This article explains where the history comes from, what the application keeps on your computer, and when exactly a run counts as long.

Why Agent’s own history is short

SQL Server Agent limits its job history. By default msdb.dbo.sysjobhistory holds 1,000 rows for the whole instance and 100 rows per job. Every step of a run takes a row, and the outcome of the run takes one more.

A job with three steps that runs every 15 minutes writes 384 rows a day. It reaches the limit of 100 rows per job in about six hours. On a busy instance msdb therefore holds a few days of a job’s past, or less. That is too little to say what is normal for a weekly job.

The local archive

With Keep a local archive of job outcomes switched on, which is the default, the application merges the finished runs it reads into an archive on your computer. Baselines and the timeline then do not stop where Agent’s limit cuts the history off.

• Where. %LOCALAPPDATA%\SQLTreeo\JobTimeline\history, one compressed file per instance or contained availability group. The folder is separate from the application, so updating or uninstalling does not remove the archive. Settings → Reading the server shows the folder and has a button to open it.
• What is kept per run. The job, the start, the duration, the outcome and the server that executed the run. For a run that did not succeed also its message and the failing step with its message. Running jobs are archived when they have finished.
• How long. 400 days. Older runs are dropped.
• Nothing is written to the server. The archive exists on your computer only.

A run that msdb no longer has is marked as coming from the local archive in the History view. Its detail shows the failing step, not every step.

The archive is kept per Windows user on one computer. It is not shared with colleagues, and it holds only what the application has read: a run that was written and removed from msdb while the application was closed is not in it. The application is not a service. It reads while it is open, and the automatic refresh pauses while the window is minimized.

Large workspaces: the archive is written less often

Reading, merging and compressing a file per server is most of the work of a full read. With many servers the archive files are therefore brought up to date less often:

Servers in the list	The archive is written
Up to about 50	With every full read, every 10 minutes by default
100	Every 20 minutes
300 or more	Once an hour

The rule is 12 seconds per server, with a maximum of one hour. In between, the application keeps what it read in memory, so nothing is missing while it runs.

The consequence: when you close the application, the runs read since the last archive write are not in the archive yet. At the next start they are read from msdb again. They are lost only when Agent’s own limit has removed them from msdb by then.

Settings → Reading the server says what applies to your workspace, for example the local archive is written every 60 minutes.

Days of job history

Settings → Reading the server has the setting Read … days of job history, 90 by default. It is the period the views show: that many days are read from msdb and loaded from the archive. With 0, everything msdb has is read.

Older runs in the archive are loaded only as far as the baselines need them: per job the newest 200 successful runs from before the period. A weekly job therefore keeps a baseline when the period is short.

A shorter period means a faster first read of a server and less memory.

When is a run long?

Every job is measured against its own norm, not against a fixed threshold. The norm is the job’s baseline: the durations of its most recent successful runs, 200 at most. Failed runs are left out, because they often stop early. A job has a baseline once it has 5 successful runs; until then the Long-running view says how many it has so far.

The Long-running view prints the rule at the top:

A run is long when it exceeds all three: the job’s median × 1.5, the median + 1 min, and the median + 3 × its normal spread (MAD). Critical: twice the median and longer than ever seen. A baseline needs 5 successful runs.

In other words, a run is long when it took longer than the largest of three values:

1. The median × 1.5. A margin in proportion to the job.
2. The median + 1 minute. This keeps very short jobs quiet: a job of 4 seconds that takes 7 seconds is not reported.
3. The median + 3 × 1.4826 × MAD. The job’s normal spread. MAD is the median absolute deviation: the median of the distances between each run and the median. Multiplied by 1.4826 it is comparable to a standard deviation, but a few outliers do not move it. A job whose duration varies from day to day gets a wider margin.

The largest of the three is the Long after column. A long run is Critical when it also took at least twice the median and longer than the longest run in the baseline.

Three examples:

Job	Median	MAD	Median × 1.5	Median + 1 min	Median + 3 × 1.4826 × MAD	Long after
A check of 12 seconds	12 s	1 s	18 s	1 m 12 s	16 s	1 m 12 s
A steady load of 20 minutes	20 m	1 m	30 m	21 m	24 m 27 s	30 m
A load of 20 minutes that varies	20 m	4 m	30 m	21 m	37 m 47 s	37 m 47 s

The 95th percentile is shown in the baseline, but it is not part of the rule. A job that slowed down over its last runs raises its own 95th percentile and would hide the slowdown.

The same rule is used everywhere:

• Long-running lists the long runs and the baseline of every job, with the trend: the median of the last five runs against the baseline median. +12% means the job is getting slower.
• Running now applies it to the time a job has been running so far.
• Problems adds the long runs with Include long runs.
• Rhythm colors a run that is longer than its norm.
• Servers counts the running jobs that are past their threshold in the column Too long.

The three numbers are under Settings → Long-running rule: the factor, the minimum extra time and the number of successful runs a baseline needs. A change recalculates every baseline at once.

For a job that decides at run time on which replica of an availability group it works, the seconds-long runs on the other replica are left out of the baseline, see Jobs in availability groups.

The row cap and its warning

Agent’s limit can be switched off, and such a server can hold millions of history rows. One read therefore takes a maximum number of rows, the newest ones. The maximum is 12 million rows for the whole workspace, divided by the number of servers in the list, and never less than 25,000 or more than 1,000,000 per server:

Servers in the list	Rows one read takes from a server
Up to 12	1,000,000
100	120,000
300	40,000
480 or more	25,000

These are rows of sysjobhistory, so steps count as well. Most servers stay far below the cap because of Agent’s own limit.

When a read reaches the cap, the server gets a note:

Instance: the job history holds more than the 40,000 rows one read takes, so the oldest runs of the period are missing. This server keeps far more history than usual (the Agent history limit is probably switched off); reading fewer days of history (Settings) avoids this.

The note appears on the server in the server list and in the Notes column of the Servers view. The newest runs are complete; the oldest days of the period are not. Lower the days of job history to get a period that fits.

Related

• Getting started for a tour of the views.
• Permissions and what is read for what else is stored on your computer.