Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cleveland DBA

Colleen Morrow is a Database Engineer for a software company in Westlake, Ohio. She has worked in the IT industry for 15+ years, doing everything from technical support to development to database administration on Informix, Oracle, and SQL Server platforms.

RYO Maintenance Plan – History Cleanup

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
		,@unit2	CHAR(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!

Comments

Leave a comment on the original post [colleenmorrow.com, opens in a new window]

Loading comments...