How to remove Database mail log history by using script

      I have created a script to remove last 30 days mail log.First i have tried to remove all logs older than

 30 days but the situation is i have a mail log of last 6 months and deleting all records at a time also 

creates a space issue while deleting. So, i have tried to delete last 30 days data and than shrink  the file 

and than remove the last 30 days data again and this way i have completed disk space
issue and purge mail history.





Use MSDB


Go

DECLARE @Deletelastmonth DATETIME

-- Remove History of last 30 Days

SELECT @Deletelastmonth = Min(Sent_date) 

FROM DBO.Sysmail_mailitems

SET @Deletelastmonth =@Deletelastmonth + 30

/*Sysmail_attachments and Sysmail_send_retries table has a foreign key from Sysmail_mailitems 

so need to remove data from both the tables first.

Need to remove attachements otherwise you can't purge mailitem history.

*/

DELETE FROM DBO.Sysmail_attachments

WHERE  Last_mod_date <= @Deletelastmonth

--Need to remove entries from retries table too. 

DELETE FROM DBO.Sysmail_send_retries

WHERE  Last_send_attempt_date <= @Deletelastmonth

EXEC Sysmail_delete_mailitems_sp @Sent_before = @Deletelastmonth

EXEC Sysmail_delete_log_sp @Logged_before= @Deletelastmonth