Script to Remove Sysmail_mailitems History.

I am getting a space issue with my system data file drive, meanwhile I have checked the size of database and I found MSDB database contains 32 GB of data. I am shocked by this information and I have eagerly checked the size of particular table and I found  “dbo.sysmail_mailitems” table contains 31 GB of data and “dbo.sysmail_attachments” contains 1 GB of data.

I had ran the query on MSDB Database to get table size.

USE MSDB;
GO

SELECT
  Schema_name(T.Schema_id) + '.' + T.Name                                  AS Tablename,
  Sum(P.Rows)                                                              AS Rowcounts,
  Cast(( Sum(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) AS Totalspacegb
FROM
  SYS.Tables T
INNER JOIN SYS.Indexes I
        ON T.Object_id = I.Object_id
INNER JOIN SYS.Partitions P
        ON I.Object_id = P.Object_id
           AND I.Index_id = P.Index_id
INNER JOIN SYS.Allocation_units A
        ON P.Partition_id = A.Container_id
INNER JOIN SYS.Filegroups Fg
        ON I.Data_space_id = Fg.Data_space_id
INNER JOIN SYS.Database_files Df
        ON Df.Data_space_id = Fg.Data_space_id
GROUP      BY
  Schema_name(T.Schema_id) + '.' + T.Name
ORDER      BY
  Totalspacegb DESC

See the Result

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

Regards,
Nirav Gajjar