search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

DBMail Housekeeping #688

Open sql-kmp opened 2 years ago

sql-kmp commented 2 years ago

Add a job that ...

I've forked the repositories and added a job for that. It utilizes dbo.sysmail_delete_mailitems_sp and dbo.sysmail_delete_log_sp:

  INSERT INTO @Jobs ([Name], CommandTSQL, DatabaseName, OutputFileNamePart01)
  SELECT 'DBMail Housekeeping',
         'DECLARE @CleanupDate datetime' + CHAR(13) + CHAR(10) + 'SET @CleanupDate = DATEADD(dd,-30,GETDATE())' + CHAR(13) + CHAR(10) + 'EXECUTE dbo.sysmail_delete_mailitems_sp @sent_before = @CleanupDate' + CHAR(13) + CHAR(10) + 'EXECUTE dbo.sysmail_delete_log_sp @logged_before = @CleanupDate',
         'msdb',
         'DBMail_Housekeeping'

Our customers use the DBMail feature extensively. When the systems came into my care, the msdb was correspondingly large. Until now, I have always added this job manually. I think this job agent complements the SQL Maintenance Solution quite well.

Pls let me know if I can send you a PR for that.

I have intentionally not changed the comments yet, especially in the header (versioning, etc.).