SQL Server - sysmaintplan_logdetail

The check for excessive sysmaintplan_logdetail has be added to the Database Health Monitor – Quick Scan Report.

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.

If you haven’t tried Database Health Monitor, now might be a good time to give it a try. Its a free application created by StedmanSolutions, LLC, and available at http://DatabaseHealth.com

SQL Server – sysmaintplan_logdetail

Related Links:

Leave a Reply Cancel reply