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