SQL Server Agent job history is missing or too short

You open the history of a job to see what happened last Friday, and it starts on Tuesday. Or a job that runs every minute shows only the last two hours. SQL Server Agent does not keep unlimited history. It keeps a fixed number of rows, and a job that runs often uses them up quickly. This article shows how to see what you have, how to raise the limit, and what to do when a longer record is needed.

Why history is short

Agent writes the history of every run to msdb.dbo.sysjobhistory: one row for the outcome of the job (step_id = 0) and one row for every step that ran. The default limits are a total of 1,000 rows for all jobs together and 100 rows for each job. Once a job reaches its 100 rows, every new run pushes the oldest one out.

The effect depends on how often a job runs and how many steps it has:

• A job with three steps that runs every five minutes writes four rows per run, so 100 rows hold about 25 runs: roughly two hours.
• A weekly job with one step writes two rows per run, so its 100 rows would hold 50 weeks. But the 1,000 rows of the total are shared by all jobs: when the total is reached, the oldest rows are removed, whichever job they belong to. Many busy jobs on one server can therefore shorten the history of a quiet one.

See how far back history goes

This query shows, per job, how many runs are kept and the oldest and newest of them:

SELECT j.name AS job_name,
       COUNT(*) AS runs_kept,
       MIN(msdb.dbo.agent_datetime(h.run_date, h.run_time)) AS oldest_run,
       MAX(msdb.dbo.agent_datetime(h.run_date, h.run_time)) AS newest_run
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.step_id = 0
GROUP BY j.name
ORDER BY oldest_run DESC;

Jobs at the top have the shortest memory. If oldest_run is only a day or two ago for jobs you want to study over weeks, the limits are too low.

Raise the limits

In SQL Server Management Studio: right-click SQL Server Agent, choose Properties, then History. Under Limit size of job history log set the maximum for the whole log and the maximum for each job. Choose numbers for the busiest job you want to keep for a period. A job that writes four rows per run and runs every five minutes needs about 1,150 rows a day. For 30 days that is 35,000 rows for that job alone, and the total has to be higher than the sum of all jobs.

The same settings are available in T-SQL through msdb.dbo.sp_set_sqlagent_properties, with the parameters @jobhistory_max_rows and @jobhistory_max_rows_per_job.

A larger history makes msdb grow. To keep it in check, set Automatically remove agent history on the same page, or purge the oldest rows regularly with msdb.dbo.sp_purge_jobhistory, for example in a maintenance job:

DECLARE @oldest datetime = DATEADD(DAY, -90, GETDATE());
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @oldest;

Check that no other job or maintenance plan purges the history sooner than you intend. A cleanup task that removes history older than a few days defeats the settings above.

Keep a record beyond the history

Even with raised limits, the history is only as long as msdb keeps it, and it is per server. Two ways to keep more:

• Copy it to your own table on a schedule, with the query above extended to all columns you want. Then the history can outlive any purge, and you can also keep it for a server that is rebuilt.
• Let a tool keep the archive. SQLTreeo Job Timeline stores the runs it has read in an archive on your computer. Each read adds the runs that are new, so the baselines and the timeline can look back further than what msdb still holds. The archive grows from the day you start using it: it cannot recover history that msdb had already thrown away. It is kept per Windows user on one PC and is not shared with colleagues. Job history, baselines and long runs explains how the archive and the baselines work.

Once history is long enough, you can ask better questions of it: find jobs that run longer than usual and find jobs that overlap.