On the First Day…

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.

[codesyntax lang="tsql"]


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.

[codesyntax lang="tsql"]



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.

2 Comments - Leave a comment
  1. [...] Maint Plan Logs – 1st Day [...]

  2. [...] Maint Plan Logs – 1st Day [...]

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

December 2012
« Nov   Jan »


  • @n9ljx: sorry (3/2) description is 'Method not found:System.Collections.Generic.IEnumerable`1<http://t.co/IgtErEl1Er.SqlParser.Par #sqlhelp
  • @muad_dba: @SQL_Padre what do you have max server memory set at? #sqlhelp
  • @n9ljx: gives cryptic error about a deprecated sp, but the sp isn't listed. How do I find the broken sp? #sqlhelp (2/2)
  • @n9ljx: on a 2008r2 instance we have several databases in 90 compatibility mode. running upgrade advisor on these DBs (1/2) #sqlhelp
  • @Ko_Ver: this time a lookup with no cache caused the issue. I guess the system is being haunted with an anti-RBAR spirit... #sqlhelp
  • @Ko_Ver: yet another package with the same issue. This time no OLE DB command. Weird stuff is going on... #sqlhelp
  • @Ko_Ver: another similar package with an OLE DB command is suddenly facing the same issue. I wish I could punch #ssis in the face... #sqlhelp
  • @SQL_Padre: #SQLHelp all T-SQL commands are queued and will not execute until application is forced closed --2/2
  • @SQL_Padre: #SQLHelp Can HEAVY read I/O cause enough CPU (or memory) pressure to cause the OS to stop working? No RDP or console login -- 1/2
  • @jeffrush: SSIS row count / script component issue (video for more detail) https://t.co/l2gFKxUUW8 #sqlhelp #ssishelp

Welcome , today is Tuesday, September 23, 2014