Stored Procedure to Purge MSDB Job History

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
USE [YourDatabaseName]
GO
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]
GO
–Create Procedure
–Parameter @DaysToPurge int — This is the number of days back data to be kept
CREATE PROCEDURE usp_PurgeMSDBHistory
@DaysToPurge as int
AS
BEGIN
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
END
GO

Thanks and Regards,

Manjunath C Bhat.

Suggestions and comments are always welcome.

Advertisements