CREATE PROCEDURE dbo.usp_purge_jobhistory
@HistoryCountPerJobToKeep INT = 10000,
@oldest_date DATETIME = NULL
AS
SET NOCOUNT ON
SET @oldest_date = ISNULL(@oldest_date, DATEADD(MONTH, -6, GETDATE()))
DECLARE @SQL VARCHAR(MAX)
IF @HistoryCountPerJobToKeep > 0
BEGIN
;
WITH DJ
AS (SELECT job_id,
RunDate,
ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY RunDate DESC) AS RowNum
FROM msdb..sysjobhistory jh (NOLOCK)
CROSS APPLY (SELECT CAST(CAST([run_date] AS VARCHAR) AS DATETIME)
+ CAST(CAST(STUFF(STUFF(RIGHT('00000000' + CAST([run_time] AS VARCHAR), 6),
3, 0, ':'), 6, 0, ':') AS TIME) AS DATETIME) AS RunDate) d
WHERE RunDate > 0
GROUP BY job_id,
RunDate)
SELECT @SQL = Util.dbo.StringConcat('EXEC msdb.dbo.sp_purge_jobhistory
@job_id = ''' + CAST(dj.job_id AS VARCHAR(256)) + ''',
@oldest_date = ''' + CAST(dj.RunDate AS VARCHAR) + '''
', '')
FROM dj
WHERE RowNum = @HistoryCountPerJobToKeep
END
IF @oldest_date IS NOT NULL
EXEC msdb.dbo.sp_purge_jobhistory
@oldest_date = @oldest_date