Agent Settings

Overview

This is the one page in the msdb suite whose data is not in msdb. Agent keeps its own settings in the registry, which is why SSMS reads them the same way this page does, through xp_instance_regread.

The Agent Settings page: the history limit and what it costs
The whole page. Forty two jobs and 990 rows of history against a limit of 1,000, with a verdict beside every setting.

Where to find it

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

Requirements

Requirement Why
sysadmin xp_instance_regread needs it. Everything else in the msdb suite works without it; only this page does not.

A login without it is told that rather than being shown defaults as though they were this instance’s answers. See Messages you may see.

The setting that pays for the page

Agent trims sysjobhistory to a row count, not to an age. Both ways of setting it are traps:

How it is set What happens
Switched off sysjobhistory grows until somebody notices msdb is forty gigabytes. msdb Space and Retention shows that as a big table and cannot say why. This page can.
Left at the default of 1000 rows An instance running fifty jobs an hour keeps about twenty minutes of history. Every page in this product that reads job history is then reporting on twenty minutes, and Job History looks empty for a reason nobody can see.

There is a second limit beside it, the maximum rows per job, and a job that runs every minute hits that one first.

The rest of the page

The other settings are the ones that change how the rest of the msdb suite should be read:

Setting Why it is here
What counts as idle An on idle schedule fires when the instance has been below a CPU threshold for a defined period. Missed Runs cannot predict those, and this is the setting that says why.
Restart SQL Server automatically Agent as a service watchdog.
Restart Agent if it stops unexpectedly Whether the holes in the Agent lane on Agent Activity close themselves.
Job shutdown timeout How long Agent waits for a running job when it is asked to stop.
Agent error log level and path What Agent records about itself, and where.
Master server / target server Whether this instance is a target of a multiserver Agent somewhere else, which means jobs here are defined elsewhere.
Mail profile The profile Agent uses for notifications. Agent does not use the default profile, it uses this one.
Fail safe operator The operator told when everything else has failed.

Reading the chart

The bars: history held now, the limit Agent trims to, and the busiest single job
History held now against the limit Agent keeps, with the busiest job against its own per job limit underneath. A job at the per job limit is trimmed whatever the total limit says.

Three bars: the history Agent is holding at this moment, the limit it trims to, and the busiest single job against the separate per job limit.

That comparison is the one the page exists for, and the line under the header says what it costs. On this instance the limit is holding one day of history, which is all any job page in this product can see.

Reading the grid

The grid: every setting, what it decides and what it means here
Value, what the setting decides, and a verdict for this instance. The colored verdicts are the ones to read: the table is at the limit, and Agent sends no mail.
Column What it is
Setting The name, as SSMS spells it.
Value What this instance has, or not set.
What it decides One sentence saying what the setting actually controls.
Verdict What this value means here, and whether it is the default, the trap, or fine.

The toolbar

Control What it does
Space and retention Opens msdb Space and Retention, which is where the history limit shows its consequence.
Agent activity Opens Agent Activity.
Mail setup Opens Database Mail Setup, for the profile named here.

Where the data comes from

master.dbo.xp_instance_regread against HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent, which xp_instance_regread rewrites to the correct hive for a named instance. That rewriting is the reason the page uses xp_instance_regread rather than xp_regread.

Messages you may see

This login cannot read Agent’s own settings. Agent keeps them in the registry rather than in msdb, and xp_instance_regread needs sysadmin. Everything else in the msdb suite works without it; only this page does not.

Report Why you would go there
msdb Space and Retention What the history limit has done to the size of msdb.
Agent Activity The service, its sessions and what is running.
Missed Runs Where the idle definition matters.
Database Mail Setup Whether the profile Agent is pointed at can actually send.
Alerts and Operators Where the fail safe operator belongs in the notification chain.

Frequently asked questions

Job History is empty and jobs definitely ran last night. Look at the history row limit here. At the default of 1000 rows, a busy instance keeps minutes.

Should I switch the limit off? Only with a purge in place. Switching it off with nothing trimming sysjobhistory is the most common reason msdb grows without bound.

Agent has a mail profile and job failure notifications still do not arrive. Agent’s mail profile being set is one of three things that have to be true. Database Mail Setup covers the other two, and Database Mail History says whether anything was sent.

Why does the page need sysadmin when nothing else in the suite does? Because the settings are in the registry rather than in a table, and reading the registry from T-SQL is a sysadmin operation whatever is being read.