When a Maintenance Table Becomes a Maintenance Problem

SQL Server Agent writes a row to msdb.dbo.sysjobhistory for every job step it runs, so the table grows quickly on busy instances once the Agent history limits are raised, disabled or left to fall behind. Left unchecked it can reach tens or hundreds of millions of rows, degrading msdb performance and causing cascading issues across your SQL Server environment. The fix is to set sensible history limits in SQL Server Agent properties and purge old rows with sp_purge_jobhistory on a nightly schedule.

Most DBAs don't think about msdb.dbo.sysjobhistory until it's already causing problems. Then one day a monitoring alert fires, SSMS grinds to a halt trying to load job history, or a vendor query starts consuming memory grants large enough to destabilise the instance. By that point, sysjobhistory growth has quietly become a production issue.

This article walks through why it happens, how to measure the problem, and the practical steps you can take to get it under control.

Why Does sysjobhistory Growth Get Out of Control?

The root cause is straightforward. SQL Server Agent writes a row to msdb.dbo.sysjobhistory for every job step execution, plus a summary row for the job itself. On a busy instance running dozens of scheduled jobs, that adds up fast.

Consider a typical mid-size environment: 50 Agent jobs, many running every 5 minutes. That's potentially 14,400 step-execution rows per job per day. Multiply across 50 jobs and you're looking at 720,000 rows daily before you've even accounted for multi-step jobs, which write one row per step plus the parent summary. Environments running frequent log backups, integrity checks, or third-party monitoring agents are especially vulnerable.

The default SQL Server Agent history settings don't help. Out of the box, SQL Server Agent limits history to 1,000 rows in total and 100 rows per job. That sounds reasonable until you realise those limits are enforced by a cleanup process that runs when the Agent service starts and when new history is written. On instances that rarely restart and have hundreds of jobs, the cleanup can fall badly behind, or in some configurations, the limits get changed by well-meaning administrators who wanted more history and never revisited the decision.

The real danger zone is when someone sets the limits very high, or disables them entirely. One real-world case documented by SQL performance specialists showed sysjobhistory exceeding 100 million rows, at which point viewing job history through SSMS became effectively impossible and queries against the table were generating memory grants large enough to trigger out-of-memory conditions.

How Do You Measure the Current Size of sysjobhistory?

Before making any changes, establish a baseline. Run this query against msdb to understand what you're dealing with:

USE msdb;
GO

SELECT
    COUNT(*) AS TotalRows,
    MIN(run_date) AS OldestRecord,
    MAX(run_date) AS NewestRecord,
    DATEDIFF(DAY,
        CAST(CAST(MIN(run_date) AS VARCHAR(8)) AS DATE),
        CAST(CAST(MAX(run_date) AS VARCHAR(8)) AS DATE)
    ) AS HistorySpanDays
FROM dbo.sysjobhistory;

Then break it down by job to find the biggest contributors:

USE msdb;
GO

SELECT
    j.name AS JobName,
    COUNT(h.instance_id) AS RowCount,
    MIN(h.run_date) AS OldestRun,
    MAX(h.run_date) AS LatestRun
FROM dbo.sysjobhistory h
INNER JOIN dbo.sysjobs j ON h.job_id = j.job_id
GROUP BY j.name
ORDER BY COUNT(h.instance_id) DESC;

If you're seeing row counts above 500,000 total, or individual jobs with more than 50,000 rows, you have a sysjobhistory growth problem worth addressing. At 10 million rows or more, you're in territory where the table is actively degrading instance performance.

Also check the size of msdb itself. A bloated sysjobhistory is one of the most common reasons msdb grows unexpectedly:

USE msdb;
GO

EXEC sp_spaceused 'dbo.sysjobhistory';

How Do You Configure SQL Server Agent History Retention Limits?

SQL Server Agent provides built-in controls for history retention. These are the first lever to pull, and they're often misconfigured or ignored entirely.

Using SQL Server Agent Properties in SSMS:

  1. In Object Explorer, right-click SQL Server Agent and select Properties.
  2. Navigate to the History page.
  3. Check "Limit size of job history log" and set "Maximum job history log size (rows)" to a value appropriate for your environment. A reasonable starting point for most environments is 10,000 to 50,000 rows total.
  4. Set "Maximum job history rows per job" to 500 to 1,000 rows per job.
  5. Click OK.

Using T-SQL directly:

USE msdb;
GO

EXEC msdb.dbo.sp_set_sqlagent_properties
    @jobhistory_max_rows = 10000,
    @jobhistory_max_rows_per_job = 500;

These settings take effect immediately and the cleanup runs automatically going forward. However, they won't immediately purge the existing backlog. For that, you need a manual purge.

How Do You Purge Existing sysjobhistory Records Safely?

SQL Server provides the system stored procedure sp_purge_jobhistory for this purpose. Don't go deleting rows directly from sysjobhistory. The procedure handles the cleanup correctly and respects job relationships.

To purge all history older than 30 days:

USE msdb;
GO

EXEC dbo.sp_purge_jobhistory
    @oldest_date = '2025-05-01';  -- Adjust to your target date

To purge history for a specific job only:

USE msdb;
GO

EXEC dbo.sp_purge_jobhistory
    @job_name = 'DatabaseBackup - USERDB - LOG',
    @oldest_date = '2025-05-01';

If you're dealing with tens of millions of rows, purging everything at once will cause a massive transaction log event in msdb and can lock up the table for an extended period. Instead, purge in batches using a loop:

USE msdb;
GO

DECLARE @BatchDate DATETIME;
DECLARE @BatchSize INT = 30; -- purge 30 days at a time

SET @BatchDate = '2024-01-01'; -- start from your oldest acceptable date

WHILE @BatchDate < DATEADD(DAY, -30, GETDATE())
BEGIN
    EXEC dbo.sp_purge_jobhistory
        @oldest_date = @BatchDate;

    SET @BatchDate = DATEADD(DAY, @BatchSize, @BatchDate);

    WAITFOR DELAY '00:00:05'; -- brief pause between batches
END

This approach keeps individual transactions manageable and avoids prolonged blocking on msdb.

Should You Implement an Automated Purge Job?

Yes, absolutely. Relying on the built-in row limits alone isn't sufficient in environments with high job frequency. The limits help, but a scheduled purge job gives you precise, predictable control over retention.

Create a SQL Server Agent job that runs sp_purge_jobhistory nightly, retaining only the history you actually need. Most operational teams need 30 to 90 days of job history for troubleshooting and compliance purposes. Keeping 12 months of history for a job that runs every 5 minutes is rarely justified.

A practical automated purge approach:

USE msdb;
GO

-- Retain 60 days of history, purge everything older
DECLARE @CutoffDate DATETIME;
SET @CutoffDate = DATEADD(DAY, -60, GETDATE());

EXEC dbo.sp_purge_jobhistory
    @oldest_date = @CutoffDate;

Schedule this as a nightly Agent job, running during off-peak hours. This is a standard component of any well-structured SQL Server maintenance plan.

One additional consideration: if you're running Ola Hallengren's maintenance solution (the industry standard for SQL Server maintenance), note that it generates significant Agent job history by design. Tuning the retention settings specifically for those jobs is worth doing separately.

What Are the Performance Impacts of an Oversized sysjobhistory Table?

This is where the problem stops being just a storage concern and becomes a stability issue.

Queries against sysjobhistory, whether from SSMS, monitoring tools, or vendor applications, can generate enormous memory grants when the table statistics are stale or when the query plan underestimates row counts. Aaron Bertrand documented a case where a vendor query against a bloated sysjobhistory was generating memory grants large enough that SQL Server was effectively shutting itself down to protect system memory. That's an extreme outcome, but it illustrates what happens when a "housekeeping" table is left unmanaged.

Beyond memory grants, a large sysjobhistory table causes:

  • Slow SSMS job history views, sometimes timing out entirely
  • Increased msdb backup times and msdb database file growth
  • Slower SQL Server Agent startup as it processes the history tables
  • Index fragmentation on msdb system tables, which rarely get maintained

The fix for the performance side is the same as the fix for the growth side: purge the data, set sensible limits, and automate the ongoing maintenance.

Key Takeaways

  • sysjobhistory grows indefinitely without intervention. The default SQL Server Agent row limits are often insufficient or misconfigured in production environments.
  • Measure before you purge. Query the table to understand row counts, history span, and which jobs are the biggest contributors before making changes.
  • Use sp_purge_jobhistory, not direct deletes. The system procedure handles cleanup correctly. Batch your purges if you're dealing with millions of rows.
  • Automate ongoing retention. A nightly purge job retaining 30 to 90 days of history is the right approach for most environments. Set it and verify it's running.
  • Oversized sysjobhistory is a performance risk, not just a storage issue. Memory grant problems and SSMS instability are real consequences of leaving this unchecked.

What to Do Next

If you're not certain what's in your sysjobhistory, or how your SQL Server Agent history settings are configured, it's worth finding out before it becomes an incident. In our experience reviewing SQL Server environments across Australian businesses, misconfigured or unmanaged Agent history is one of the most consistently overlooked maintenance gaps.

DBA Services offers comprehensive SQL Server health checks that cover exactly this kind of configuration drift, including Agent settings, msdb growth, maintenance job coverage, and dozens of other operational risk areas. If you'd like an independent assessment of your SQL Server environment, get in touch with the DBA Services team to find out what a health check would look like for your organisation.