Quick Scan Report – Extended Sysmaintplan_logdetail

What this check looks for

The row count in msdb.dbo.sysmaintplan_logdetail, reported when it has grown large. The check is skipped on Amazon RDS.

Why it matters

Maintenance plans log every subtask of every execution, and nothing removes those rows unless you have explicitly added a History Cleanup Task.

The volume is easy to underestimate. A maintenance plan that rebuilds indexes across 30 databases writes a log detail row per database per run. A plan with four tasks across the same 30 databases writes 120 rows a night. Over three years that is around 130,000 rows, and plans that run more frequently produce far more.

What that costs:

• msdb grows, alongside the backup history and job history that accumulate in the same database.
• The maintenance plan reporting in Management Studio gets slow, because it reads this table.
• The plans themselves slow down, since each execution inserts into a growing table.
• msdb backups and integrity checks take longer.
• The sysmaintplan_logdetail table holds long text in its line1 through line5 columns, so the size per row is larger than a typical log table.

None of this is urgent, which is why the finding is low severity. It is housekeeping, and the reason to do it is that it compounds with the other msdb accumulation findings: backup history, job history, and log shipping history all live in the same database and all grow for the same reason, which is that nothing purges them by default.

The underlying point is more useful than the cleanup, though. If you have maintenance plans generating this much history, it is worth asking whether the maintenance plans themselves are the right tool. Maintenance plans are the built in option and they are limited: the index task rebuilds everything regardless of fragmentation, there is no time boundary, the logging is verbose and hard to query, and error handling is minimal. A scripted maintenance solution gives you thresholds, time limits, and a log you can actually report on, and it is the usual reason this table stops growing.

How to confirm it yourself

The size of the problem:

SELECT COUNT(*)              AS [rows],
       MIN([start_time])     AS [oldest_entry],
       MAX([start_time])     AS [newest_entry]
  FROM msdb.dbo.sysmaintplan_logdetail WITH (NOLOCK);

What it occupies:

USE [msdb];
GO
SELECT OBJECT_NAME(p.[object_id]) AS [table_name],
       SUM(p.[rows])              AS [rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb]
  FROM sys.partitions            AS p WITH (NOLOCK)
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON a.[container_id] = p.[partition_id]
 WHERE OBJECT_NAME(p.[object_id]) LIKE 'sysmaintplan%'
   AND p.[index_id] IN (0, 1)
 GROUP BY p.[object_id]
 ORDER BY [size_mb] DESC;

Which plans are generating it:

SELECT p.[name]        AS [plan_name],
       sp.[subplan_name],
       COUNT(*)        AS [log_rows],
       MIN(ld.[start_time]) AS [oldest],
       MAX(ld.[start_time]) AS [newest]
  FROM msdb.dbo.sysmaintplan_logdetail AS ld WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysmaintplan_log  AS l  WITH (NOLOCK)
         ON l.[task_detail_id] = ld.[task_detail_id]
 INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp WITH (NOLOCK)
         ON sp.[subplan_id] = l.[subplan_id]
 INNER JOIN msdb.dbo.sysmaintplan_plans    AS p  WITH (NOLOCK)
         ON p.[id] = sp.[plan_id]
 GROUP BY p.[name], sp.[subplan_name]
 ORDER BY [log_rows] DESC;

Whether any plan has a History Cleanup Task, which is the thing that should have prevented this:

SELECT j.[name] AS [job_name], st.[step_name], st.[command]
  FROM msdb.dbo.sysjobsteps AS st
 INNER JOIN msdb.dbo.sysjobs AS j ON j.[job_id] = st.[job_id]
 WHERE st.[command] LIKE '%sp_maintplan_delete_log%'
    OR st.[command] LIKE '%sp_delete_backuphistory%'
    OR st.[command] LIKE '%sp_purge_jobhistory%';

An empty result is the explanation for all three msdb accumulation findings at once.

And what failed, which is worth a look while you are in here:

SELECT TOP (50)
       p.[name] AS [plan_name],
       ld.[start_time],
       ld.[end_time],
       ld.[succeeded],
       ld.[error_number],
       ld.[error_message]
  FROM msdb.dbo.sysmaintplan_logdetail AS ld WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysmaintplan_log  AS l  WITH (NOLOCK)
         ON l.[task_detail_id] = ld.[task_detail_id]
 INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp WITH (NOLOCK)
         ON sp.[subplan_id] = l.[subplan_id]
 INNER JOIN msdb.dbo.sysmaintplan_plans    AS p ON p.[id] = sp.[plan_id]
 WHERE ld.[succeeded] = 0
 ORDER BY ld.[start_time] DESC;

Failed maintenance subtasks nobody has looked at are a better finding than the table size.

How to fix it

Purge the old rows, then schedule the purge so it does not come back.

The supported procedure:

EXEC msdb.dbo.sp_maintplan_delete_log
     @plan_id    = NULL,
     @subplan_id = NULL,
     @oldest_time = '2025-06-15';

NULL for both IDs covers every plan. Pass a date 90 days back.

On a very large table, work in increments, the same way as the backup history purge:

DECLARE @cutoff DATETIME = (SELECT MIN([start_time]) FROM msdb.dbo.sysmaintplan_logdetail);
DECLARE @target DATETIME = DATEADD(DAY, -90, GETDATE());

WHILE @cutoff < @target
BEGIN
    SET @cutoff = DATEADD(MONTH, 1, @cutoff);
    IF @cutoff > @target SET @cutoff = @target;

    EXEC msdb.dbo.sp_maintplan_delete_log @plan_id = NULL, @subplan_id = NULL,
                                          @oldest_time = @cutoff;
END;

Then schedule it, along with the other msdb cleanups, so all three findings stay fixed:

EXEC msdb.dbo.sp_add_job @job_name = N'DBA - Purge msdb History';

EXEC msdb.dbo.sp_add_jobstep
     @job_name = N'DBA - Purge msdb History',
     @step_name = N'Purge maintenance plan log',
     @subsystem = N'TSQL', @database_name = N'msdb',
     @command = N'DECLARE @d DATETIME = DATEADD(DAY, -90, GETDATE());
                  EXEC sp_maintplan_delete_log @plan_id = NULL, @subplan_id = NULL,
                                               @oldest_time = @d;';

EXEC msdb.dbo.sp_add_jobstep
     @job_name = N'DBA - Purge msdb History',
     @step_name = N'Purge backup history',
     @subsystem = N'TSQL', @database_name = N'msdb',
     @command = N'DECLARE @d DATETIME = DATEADD(DAY, -90, GETDATE());
                  EXEC sp_delete_backuphistory @oldest_date = @d;';

EXEC msdb.dbo.sp_add_jobstep
     @job_name = N'DBA - Purge msdb History',
     @step_name = N'Purge job history',
     @subsystem = N'TSQL', @database_name = N'msdb',
     @command = N'DECLARE @d DATETIME = DATEADD(DAY, -90, GETDATE());
                  EXEC sp_purge_jobhistory @oldest_date = @d;';

EXEC msdb.dbo.sp_add_jobschedule
     @job_name = N'DBA - Purge msdb History',
     @name = N'Weekly Sunday', @freq_type = 8, @freq_interval = 1,
     @freq_recurrence_factor = 1, @active_start_time = 030000;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'DBA - Purge msdb History';

Or add a History Cleanup Task to the maintenance plan itself, which is the built in route and does the same three cleanups in one task. Either approach works; a single scripted job is easier to see and to audit.

Reclaim the space once it is done:

USE [msdb];
GO
DBCC SHRINKFILE (N'MSDBData', 0, TRUNCATEONLY);

Then consider the plans themselves. If the index task is rebuilding every index every night regardless of fragmentation, it is generating both this log volume and a great deal of unnecessary I/O and transaction log. A scripted maintenance solution with fragmentation thresholds and a time limit produces less of everything, including this table.

How long it takes

About an hour to purge and schedule. Reviewing whether the maintenance plans should be replaced is a separate and larger question.

Related reports

Related checks

Frequently asked questions

Is there a supported way to purge this? Yes, sp_maintplan_delete_log. Do not delete from the table directly, since the rows are related across sysmaintplan_log and sysmaintplan_logdetail.

How much should I keep? 90 days is generous for maintenance plan logging. The rows are only useful for investigating a recent failure.

Does a History Cleanup Task do this? Yes, and it also purges backup and job history. Adding one to an existing plan is the built in route; a single scripted job covering all three is easier to audit.

Should I be using maintenance plans at all? They are the built in option and they work. A scripted maintenance solution gives you fragmentation thresholds, time limits and a queryable log, which reduces both the maintenance window and the volume of this table.