Clear Maintenance Plan History (nibble delete)

Similar to Clear Backup History (nibble delete) and Clear Job History (nibble delete), this third script applies the same technique to the maintenance plan history table.

-- Maintenance Plan History

USE MSDB
GO

DECLARE @OldestJobHistoryDate DATETIME
DECLARE @DaysToLeave INT
DECLARE @DaysToDeleteAtOnce INT
DECLARE @DeleteDate DATETIME
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)

SELECT @OldestJobHistoryDate = end_time 
FROM msdb.dbo.sysdbmaintplan_history 
WHERE sequence_id = (select MIN(sequence_id) FROM msdb.dbo.sysdbmaintplan_history )

SELECT @OldestJobHistoryDate
SET @DaysToLeave = 30
SET @DaysToDeleteAtOnce = 1

SELECT @Counter = DATEDIFF(DAY,@OldestJobHistoryDate,GETDATE())

WHILE @Counter >= @DaysToLeave  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),GETDATE(),21) + ' processing ' + CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21)
 SELECT @DeleteDate = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 RAISERROR (@CounterText , 10, 1) WITH NOWAIT   
 DELETE FROM msdb.dbo.sysdbmaintplan_history WHERE end_time < @DeleteDate 
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 
GO