The last step in our campaign to rid the world of maintenance plans deals with housekeeping: the history cleanup step. I know. Cleanup is never the sexiest of jobs, but just like the dishes or the laundry, it still needs to be done occasionally.

If you were to create an true maintenance plan and include this task, what SQL Server would really do under the covers is execute a couple of built-in stored procedures. And that’s all I’m doing in my procedure, too.

Every time we perform a database backup or restore, records are written to the system tables in msdb, like backupfile, backupfilegroup, backupset, restorefile, restorefilegroup, and restorehistory. Over time, these records can cause some bloat in the msdb database, so it’s recommended that we clean up older records. That’s what sp_delete_backuphistory does. It accepts a single datetime parameter and deletes history records older than that date.

Similarly, whenever a SQL Server Agent job is executed, a log record is written to the sysjobhistory table in msdb. To keep that table in check, we use sp_purge_jobhistory. (OCD moment: why is one “delete” and one “purge”?) This particular procedure accepts 3 parameters, job name, job id, and datetime, but I’m only going to use the last parameter for my procedure.

My procedure is simple. It only accepts 2 parameters: an integer representing the retention period, and a character field that specifies the unit that period is measured in. Three days, for example, or four weeks. So, to delete all history that’s more than four weeks old, we would execute the following:

 exec maint.sp_clean_history @period=4, @unit='W' 

Easy, right? The procedure code is just as straightforward. It might even look a little familiar, it’s very similar to the backup file cleanup procedure I presented earlier.

CREATE PROCEDURE [maint].[sp_clean_history]
(@period smallint = 4, @unit char(1) = 'W')
AS
BEGIN
DECLARE
 @deldate DATETIME  -- calculated date for cutoff
,@unit2CHAR(2) -- cleaned up interval unit
,@sqlstmt NVARCHAR(1000)
,@params NVARCHAR(255)
SET NOCOUNT ON
BEGIN TRY
SELECT
 @unit2 = CASE @unit
WHEN 'H' THEN 'hh'
WHEN 'D' THEN 'dd'
WHEN 'W' THEN 'ww'
WHEN 'M' THEN 'mm'
WHEN 'Y' THEN 'yy'
ELSE @unit
END
,@period = CASE
WHEN @period > 0 THEN @period*-1
ELSE @period
END
IF @unit2 NOT IN ('hh', 'dd', 'ww', 'mm', 'yy')
RAISERROR (N'Invalid interval unit specified.  Accepted values are H,D,W,M,Y.'
,16
,1)
SET @params = '@Units int, @dtOutput datetime OUTPUT'
SET @sqlstmt = 'set @dtOutput = Dateadd(' + @unit2 + ',@Units, GetDate())'
EXEC sp_executesql @sqlstmt, @params, @Units = @period, @dtOutput = @deldate OUTPUT
EXEC msdb.dbo.sp_delete_backuphistory @deldate
EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date=@deldate
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return
-- error information about the original error that
-- caused execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
   @ErrorSeverity, -- Severity.
   @ErrorState -- State.
   );
END CATCH
END 

So there you have it. The last task of our home-grown “maintenance plan” is complete. All we need to do now is create the appropriate jobs to execute our procedures as we see fit. I’ll leave that to you, but I do have one recommendation. A few of these tasks, the integrity check especially, will benefit from some logging. To handle this, I write the output from that job step to an output file in my default errorlog directory. If you’re not familiar with this, it’s on the Advanced tab of the New Job Step window.

I hope you’ve enjoyed this series. If you have any suggestions for how to improve these procedures, please feel free to comment!