SQL Server: Fixing another huge MSDB database – 80GB+

I have blogged several times about unusually large MSDB databases like here.
Most of the time the problem comes from the backup history which is never purged.
This time it’s different. Try to guess, if not the backup history, what can cause MSDB to increase abnormally in size?

Let’s start by looking at the total size of the database:

It’s huge. The database size is almost 90GB. The 10% configuration on data files causes large 8GB autogrowth increments.

Now let’s take a look at the storage used for each table:

The problem comes from emails sent by the application directly through Database Mail. These emails have a very large HTML body.

Here are some information about the emails stored in this msdb.

select CAST(MIN(send_request_date) AS DATE) AS oldestEmail, Count(*) AS nbRows
from msdb.dbo.sysmail_allitems

oldestEmail nbRows
----------- -----------
2020-04-06  43935

There aren’t a lot of emails. There’s only about one year of emails stored.

select Count(*) AS nbRows, AVG(DATALENGTH(body))/1048576 AS avgSizeMB
from msdb.dbo.sysmail_allitems
where DATALENGTH(body) > 1048576 -- 1MB

nbRows      avgSizeMB
----------- --------------------
297         287

There are almost 300 emails with an HTML body exceeding 1 Megabytes size. The average size for these 300 emails is a 287MB of HTML body.
This HTML size is obviously abnormal for an email and probably for any HTML file.

select SUM(IIF(sent_status='failed', 0, 1)) AS emailsSent
from msdb.dbo.sysmail_allitems
where DATALENGTH(body) > 1048576 -- 1MB

emailsSent
-----------
0

None of those large emails has been sent successfully…
Looking at the Database Mail log we can see the following error message:

The mail could not be sent to the recipients because of the mail server failure.
Exception Message: Cannot send mails to mail server. 
(Exceeded storage allocation. The server response was: 5.3.4 Message size exceeds fixed maximum message size).

Email size is often restricted to a few Megabytes to limit bandwidth usage.
These large emails have nowhere to go and are just bloating the msdb database.

I decided to delete old emails with a retention of 6 months.
To clean up this table we can use the following procedure: msdb.dbo.sysmail_delete_mailitems_sp

I used the following script to minimize the impact on the transaction log file.

use msdb
go
declare @retentionDate datetime = DATEADD(MONTH, -6, getdate())
declare @oldest_date datetime = (select min(send_request_date) from msdb.dbo.sysmail_allitems)
 
while (@oldest_date  < @retentionDate)
begin
    print 'sysmail_delete_mailitems_sp ' + CAST(@oldest_date AS varchar)
    exec msdb.dbo.sysmail_delete_mailitems_sp @oldest_date
 
    --  Delete by 1 week increments
    set @oldest_date = DATEADD(WEEK, 1, @oldest_date)
 
    checkpoint
	WAITFOR DELAY '00:00:10'
end

The application team has been informed so they can fix this email content issue.
I recommend checking the size of your system databases frequently to avoid any weird situation like this.