SQL Server: msdb too big with the table dbo.sysmaintplan_logdetail

Today, I was by a new customer and the I see that the msdb data file was over 4GB. A little too big…

To see which table are so big, in SSMS, I select the msdb database, right-click, go in Reports>Standards Reports> Disk usage by Top Tables

As you can see, the table dbo.sysmainplan_logdetail is the big one with a size of 2,3GB and 13205 records.

This table is the log for the maintenance plan that is generated through the maintenance plan wizard in the management menu in SSMS. More information here.

After googling about how to purge this table, I find a lot of articles describing a method to truncate the table.

I was not really sure that is the good way to do it…

For my experience, to purge log or history in the msdb, we have always a store procedure like sp_delete_backuphistory or sp_purge_jobhistory.

I search in all Stored Procedure in the database msdb and finally find “sp_maintplan_delete_log”.

I search also in the Microsoft documentation and and find only this sentence here

To clean up the table dbo.sysmainplan_logdetail, I use this store procedure in a loop with timestamp to not fill the T-log file and keep only the 30 last days:

DECLARE @date datetime, @sql nvarchar(1000)

SET @date = CONVERT(nvarchar(10), getdate()-720 , 21)

WHILE @date <= CONVERT(nvarchar(10), getdate()-30 , 21)

            BEGIN

                        SET @sql = N'EXEC [msdb].[dbo].[sp_maintplan_delete_log]  @oldest_time = {ts''' + CONVERT(nvarchar(23), @date , 21) + N'''}'

                        EXEC(@sql)

                        SET @date = @date +30

                        PRINT @sql

            END

5709 entries are before january 2020… 😳

After this cleanup, the table dbo.sysmainplan_logdetail  is reduced the number of entries from 13205 to 152 and the size from 2.3GB to 35MB.

Et voila. To be complete, the best is also to do the same, as written in the Microsoft documentation, with the stored procedures sp_purge_jobhistory and sp_delete_backuphistory.

I advise also to create a daily job to cleanup all histories used in your maintenance plans. 😎