Many of us have heard the song “The Twelve Days of Christmas.”  Some are familiar with how that song has some connection with the Holiday season for Christians.  Fewer are aware that this song is connected to the tradition of giving traditions on each of the days of Christmas.  Fewer yet know that the first day of Christmas is actually December 25th.

So, instead of a blog series for the 12 Days of Christmas, I am doing a blog series for the 12 Days of pre-Christmas.  Today is Day 1, or day -12, depending on how you look at it.

My caveat is that in my own little world, the 12th day of pre-Christmas will overlap with the actual first day of Christmas.  You may find that this is similar to how the First Day of Epiphany and 12th day of Christmas can overlap.

Now that we have that all squared away, on to my First day of pre-Christmas gift for you.

On the First Day of pre-Christmas…

My DBA gave to me  - A maintenance plan log.  No, not a yule log.  But a maintenance plan log.  Have you heard of sysmaintplan_log and sysmaintplan_logdetail?  These are system tables in the msdb database.

These tables are used to support logging of maintenance plans (SSIS style) since SQL 2005.  If you have not introduced yourself to them and you are running maintenance plans (SSIS style), you may have some large tables.  I have seen these tables become very large and as a result cause the msdb database to also be very large.

Part of a good maintenance plan is also to take care of the undercarriage.  In this case, we need to take good care of the supporting tables for the SSIS style maintenance plans.  One quick and easy method to do this is to add a SQL Agent job to help maintain these tables.

In the SQL Agent job that you create, add this query to maintain the amount of historical information held by these tables.

Code block

DECLARE @OldDate DATETIME
SET @OldDate = GETDATE() - 14;
 
EXECUTE msdb.dbo.sp_maintplan_delete_log @oldest_time = @OldDate

And there you have it.  Something you may also want to do is to add a Clustered Index to the sysmaintplan_logdetail table.  There are no indexes on this table.  Since the stored procedure is looking at start_time, and this is likely to be the most frequently queried field, I chose to create a Clustered Index on start_time.

Code block

CREATE CLUSTERED INDEX [CI_Start_time] ON [dbo].[sysmaintplan_logdetail] 
(
[start_time] ASC
)

Conclusion

Simple but effective.  The idea here is to decrease the chance of bloat in the msdb database.  We don’t want the database to grow out of control.  We don’t want the size of these tables to cause the Maintenance plans to run slower.  And we certainly don’t want these tables to grow to a size that causes the database to fill and subsequently cause backups to start failing (for instance).

Tune in each day between now and December 25th for a new gift in this season of giving.