Quick Scan Report – Extended Sysmaintplan_logdetail

If you haven’t had a maintenance plan to clean up maintenance plan history, it can grow excessively over time. I recently worked on a SQL Server that had 6 years worth of maintenance plan history, and the msdb.dbo.sysmaintplan_logdetail table was approaching nearly 1gb in size. It would be a tough argument to claim that you really need a 6 year history of your maintenance plan history. If you really do, you might want to copy it off to another database outside of msdb.

The problem with this type of excessive growth is that it bloats out the size of msdb, which should be a pretty small database under almost any circumstances.

Here are some queries to examine the contents of the msdb.dbo.sysmaintplan_logdetail table.

SELECT TOP 100 *
FROM msdb.dbo.sysmaintplan_logdetail WITH (NOLOCK)


SELECT count(*)
FROM msdb.dbo.sysmaintplan_logdetail WITH (NOLOCK)


SELECT TOP 1 start_time, count(1) OVER() as numOlder
FROM msdb.dbo.sysmaintplan_logdetail WITH (NOLOCK)
WHERE start_time < GETDATE() - 365
ORDER BY start_time ASC

The following sproc sp_mainplan_delete_log can be used to clean up the excessive history. You could also use the maintenance plan task to clean up maintenance plan history.

msdb.dbo.sp_maintplan_delete_log @oldest_time='3/19/2016';

The recommendation is to clean out this table regularly. As it grows, it can add excess overhead when running maintenance plan jobs.

Related Links: