Purging your MSDB Job History is sometime very important which helps you read the Jos history in GUI very easily and keeps which only required number of days. You can add this in your Maintenance Plan or using a SSIS Package. I found one of the way a Stored Procedure to do the same. This Procedure accepts a Parameter @DaysToPurge. This is the number of days back for which you want to keep the History. I have used the existing MS SP’s for this tasks.
–Drop stored procedure if it already exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PurgeMSDBHistory]’)
AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[usp_PurgeMSDBHistory]
–Parameter @DaysToPurge int — This is the number of days back data to be kept
CREATE PROCEDURE usp_PurgeMSDBHistory
@DaysToPurge as int
DECLARE @VarDate DATETIME
DECLARE @DTP as INT
SET @DTP = @DaysToPurge
SELECT @VarDate = CAST((GETDATE() – @DTP) AS DATETIME)
EXEC MSDB.DBO.sp_delete_backuphistory @VarDate
EXEC MSDB.DBO.sp_purge_jobhistory @oldest_date=@VarDate
EXECUTE MSDB..sp_maintplan_delete_log NULL,NULL,@VarDate
Thanks and Regards,
Manjunath C Bhat.
Suggestions and comments are always welcome.