Recently I had to implement a job to remove msdb mail history and backup history.
We do daily build emails and full and differential backups. Though this does not increase the size of msdb to great extent it might grow considerably over a few months or year or so.
For mail cleanup, I got a tip from the following article:
http://www.mssqltips.com/sqlservertip/1732/sql-server-database-mail-cleanup-procedures/
1) To delete mail history.
We needed to keep upto 2 weeks of old data,
But not delete more than 3 months of data at a time, since that might block up msdb.
DECLARE @MinSentDate DateTime
select @MinSentDate=MIN(sent_date) from sysmail_allitems
DECLARE @DeleteBeforeDate DateTime
Set @DeleteBeforeDate = (Select DATEADD(mm,3, @MinSentDate))
if(dateadd(DD,-14,getdate())<@DeleteBeforeDate)
Begin
set @DeleteBeforeDate=dateadd(DD,-14,getdate());
End
Print @DeleteBeforeDate
--To delete messages with any/all sent status
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate, @sent_status='sent'
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate, @sent_status='unsent'
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate, @sent_status='retrying'
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate, @sent_status='failed'
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate
Print 'Mail history deleted before:'
Print @DeleteBeforeDate
2)To delete backup history
We do full backups every sunday and differential backups for the rest of the week.
Hence it was necessary to delete backup before sunday two weeks ago, since here again we wanted to keep two weeks worth of data.
Also, we did not want to delete more than 3 months of data at a time.
DECLARE @TodayDayOfWeek INT
DECLARE @StartOfPrevWeek DateTime
DECLARE @deletedate DateTime
SET DATEFIRST 1
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the sunday of the previous week
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+7), GetDate())
--since Sunday is full backup day, we choose saturday
SET @deletedate = @StartOfPrevWeek-1
DECLARE @minbkdate DATETIME;
SET @minbkdate = (SELECT MIN(backup_start_date)
FROM backupset WITH (nolock));
print @minbkdate
SET @minbkdate = (SELECT dateadd(mm,3,@minbkdate));
IF (@deletedate < @minbkdate )
BEGIN
SET @minbkdate = @deletedate;
END;
Exec sp_delete_backuphistory @minbkdate
Print 'Backup history deleted before:'
PRINT @minbkdate
These 2 steps were put in a job and scheduled to run weekly.
