On the Third Day…

Mais Oui!

But of course, we must have some french hens for today!  Today we have the third day of pre-Christmas.  And as was mentioned we have more undercarriage work in store.

So far we have talked about the following:

1. Maintenance Plan Logs on the First Day
2. Service Broker lost Messages on the Second Day

On the Third Day of Pre-Christmas…

My DBA gave to me more old fat.  Like the past few days, we are looking at more tables that have too much fat.  The kind of fat that has been sitting around for too long and is far beyond stale.

Here, I am going to cover two more tables that can get large.  This is very much similar to how Nicholas Cain (twitter) has covered the topic, though I do it slightly different.  You can read what he has done here. *

The biggest difference comes in how we determine large tables.  You can read my method for finding large tables here.

The key here is that there are tables in the msdb database that are in use for maintaining mail history and backup history.  These tables need to be given TLC.

To maintain these tables, I do much the same as Nic does.  To maintain these two tables, I create a SQL Job to run scripts.  The scripts are as follows.

/* Cleanup old backup history */DECLARE @BackupHistoryOlderThan DATE 
SET @BackupHistoryOlderThan = DATEADD(d, -90, GETDATE())
EXECUTE msdb.dbo.sp_delete_backuphistory @BackupHistoryOlderThan

/* Cleanup old mail items */DECLARE @MailItemHistoryOlderThan DATE 
SET @MailItemHistoryOlderThan = DATEADD(d, -14, GETDATE())
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @MailItemHistoryOlderThan

Now, this is the third day of pre-Christmas so I have one more routine that Nic didn’t discuss in that post.  This one is to clean out Agent Job history.  This is done because I have seen the sysjobhistory table get rather large like the other tables I have discussed so far in this series.  To run maintenance for this table, it is rather similar to the prior two scripts from today.

/*Cleanup of Old Job History*/Declare @JobHistoryOlderThan DATE
Set @JobHistoryOlderThan = DATEADD(d, -14, GETDATE())
EXECUTE msdb.dbo.sp_purge_jobhistory @oldest_date = @JobHistoryOlderThan

The stored procedure being used can take different parameters.  It allows you to purge either for a specific job or by date or everything if no parameter is supplied.  You can read more of that from the documentation here.

Another reason I like to do this is to make it easier to read the agent job history when I am troubleshooting.  When there is a lot of history, it can be rather slow and even possibly cause timeouts trying to get in to troubleshoot a failed job.

Running this procedure comes in very handy when somebody has forgotten to set the SQL Agent properties for job history retention.  Then again, maybe they didn’t forget.

This type of setting of course is a blanket example.  As I alluded to earlier, this procedure can take different parameters.  And if somebody has not set the history retention properties for SQL Agent, then we have some flexibility.

With this flexibility, you can set a custom purge cycle for each of several different jobs.  Maybe some jobs only need 2-3 days of retention but another needs to have 90 days.  This method allows you to do that.

Upcoming

So far we have worked to maintain several different large tables within the msdb database.  In the next article, we will discuss how to release the space from the table that should be released after removing large amounts of data stored in these tables due to a lack of maintenance and TLC.

Notes

* The page previously linked to the blog for Nicholas Cain appears to now be broken. Due to that break, here are the indexes that were referenced in that blog post.

/* msdb maintenance activity related indexes */Create Index IX_maintplan_taskdetailid on [msdb].[dbo].[sysmaintplan_logdetail]
([task_detail_id])
with (fillfactor = 90)
/* Improves plan cost by 10x down from 417 to 44 in one batch.  Down from 26 to .1 in another batch*/
Create Index IX_backupset_type on msdb.dbo.backupset
([type],[backup_finish_date])
Include ([media_set_id], [database_name])
With (fillfactor = 90)
Create Index IX_jobactivity_jobid on [msdb].[dbo].[sysjobactivity]
([job_id])
Include ([session_id])
With (fillfactor = 90)