Why your SQL Agent history only goes back two days, and how to keep 90

#The symptom

You open a job's history to see when it last failed, and there's nothing older than yesterday. That's not a bug. SQL Agent trims history by row count:

Each run writes one row per step plus one row for the job outcome. A log backup job with 2 steps running every 15 minutes writes 288 rows a day, so 100 rows is about 8 hours.

-- current limits (values in the Agent registry; -1 means no limit)
EXEC msdb.dbo.sp_get_sqlagent_properties;   -- see jobhistory_max_rows and jobhistory_max_rows_per_job

-- how much history is there now?
SELECT COUNT(*) AS rows_total, MIN(run_date) AS oldest FROM msdb.dbo.sysjobhistory;
SELECT TOP 10 j.name, COUNT(*) AS rows_kept FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
GROUP BY j.name ORDER BY rows_kept DESC;

#Option 1: raise the row limits

EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = 100000, @jobhistory_max_rows_per_job = 5000;

In SSMS: SQL Server Agent → Properties → History. Simple, but still count-based, so high-frequency jobs push out the history of jobs that run monthly.

#Option 2: time-based retention (recommended)

Turn off the row limit, then purge by age with a daily job:

-- 1. remove the row limits (-1 = unlimited)
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = -1, @jobhistory_max_rows_per_job = -1;

-- 2. daily job step: keep 90 days
DECLARE @cutoff datetime = DATEADD(day, -90, GETDATE());
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @cutoff;

If you use Ola Hallengren's maintenance solution, its sp_purge_jobhistory job already does step 2 (30 days by default; change the parameter).

Warning

With limits off and no purge job, msdb grows forever. Create the purge job in the same change, and check that it runs.

#Option 3: archive history to a table

When you need a year of trend data (durations, failure rates) but want msdb small, copy history to a DBA database before it's purged:

-- one-time
CREATE TABLE dba.dbo.agent_job_history (
  instance_id int NOT NULL PRIMARY KEY, job_id uniqueidentifier NOT NULL, job_name sysname NOT NULL,
  step_id int NOT NULL, step_name sysname NOT NULL, run_status int NOT NULL,
  run_start datetime NOT NULL, run_duration_s int NOT NULL, message nvarchar(4000) NULL);

-- daily job step, before the purge
INSERT dba.dbo.agent_job_history (instance_id, job_id, job_name, step_id, step_name, run_status, run_start, run_duration_s, message)
SELECT h.instance_id, h.job_id, j.name, h.step_id, h.step_name, h.run_status,
       DATEADD(second, (h.run_time / 10000) * 3600 + (h.run_time / 100 % 100) * 60 + h.run_time % 100, CONVERT(datetime, CONVERT(char(8), h.run_date))),
       (h.run_duration / 10000) * 3600 + (h.run_duration / 100 % 100) * 60 + h.run_duration % 100,
       LEFT(h.message, 4000)
FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE h.instance_id > ISNULL((SELECT MAX(instance_id) FROM dba.dbo.agent_job_history), 0);

instance_id is an increasing identity in sysjobhistory, so "everything above the last archived ID" picks up only new rows. The table also has cleaner columns (real datetime, seconds) for reporting. Purge the archive itself on a longer schedule (for example 400 days).

Across many servers, Get-DbaAgentJobHistory from dbatools can pull history into a central database instead.

#Other msdb history to keep under control

-- msdb's biggest tables
SELECT TOP 10 t.name, SUM(p.rows) AS row_count, SUM(a.total_pages) * 8 / 1024 AS mb
FROM msdb.sys.tables t JOIN msdb.sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
JOIN msdb.sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY t.name ORDER BY mb DESC;

#Recommended setup

1. Row limits off (-1).
2. One daily "DBA - Cleanup - msdb history" job: archive Agent history, then purge Agent history older than 90 days, backup history older than 180 days, and Database Mail items older than 30 days.
3. msdb in SIMPLE recovery (the default), with backups like any system database.
4. An alert if msdb grows unexpectedly.

Tip

Decide retention from the questions you need to answer. "Did this monthly job fail last quarter?" needs at least 100 days of history for that job. A row count can't guarantee that; a date can.